Support for PostGIS Geography columns #768

Closed
artemp opened this Issue Oct 11, 2011 · 7 comments

Comments

Projects
None yet
2 participants
Owner

artemp commented Oct 11, 2011

PostGIS Datasources with Geography columns require that parameter be set declaring st_prefix=true. Otherwise, mapnik calls older PostGIS functions that don't support newer features.

The use of the st_prefix parameter is mentioned in some change sets, but isn't widely documented.

Here's a test dataset and the corresponding Datasource elements:

CREATE TABLE country_population_centroid
(
  country_cde character(2) NOT NULL,
  lat NUMERIC,
  lon NUMERIC,
  geog GEOGRAPHY(POINT,4326),
  CONSTRAINT country_idx PRIMARY KEY (country_cde)
)
WITH (
  OIDS=TRUE
);
ALTER TABLE country_centroid OWNER TO postgres

populate this with something like:

COPY country_centroid (country_cde, lat, lon, geog, country_name, population) FROM stdin;
LU      49.75   6.1666999999999996      0101000020E610000009F9A067B3AA18400000000000E04840      Luxembourg      480222
LV      57      25      0101000020E610000000000000000039400000000000804C40      Latvia  2259810
LY      25      17      0101000020E610000000000000000031400000000000003940      Libya   6036914
MA      32      -5      0101000020E610000000000000000014C00000000000004040      Morocco 33757175
MC      43.7333 7.4000000000000004      0101000020E61000009A99999999991D4012143FC6DCDD4540      Monaco  32671
MD      47      29      0101000020E61000000000000000003D400000000000804740      Moldova 4320490
ME      42      19      0101000020E610000000000000000033400000000000004540      Montenegro      684736
MG      -20     47      0101000020E6100000000000000080474000000000000034C0      Madagascar      19448815
MH      9       168     0101000020E610000000000000000065400000000000002240      Marshall Islands        61815
NZ      -41     174     0101000020E61000000000000000C0654000000000008044C0      New Zealand     4115771
OM      21      57      0101000020E61000000000000000804C400000000000003540      Oman    3204897
PA      9       -80     0101000020E610000000000000000054C00000000000002240      Panama  3242173
SM      43.7667 12.416700000000001      0101000020E6100000857CD0B359D52840EEEBC03923E24540      San Marino      29615
SN      14      -14     0101000020E61000000000000000002CC00000000000002C40      Senegal 12521851
SO      10      49      0101000020E610000000000000008048400000000000002440      Somalia 9832017
SR      4       -56     0101000020E61000000000000000004CC00000000000001040      Suriname        470784
ST      1       7       0101000020E61000000000000000001C40000000000000F03F      Sao Tome and Principe   199579
SY      35      38      0101000020E610000000000000000043400000000000804140      Syria   19314747
SZ      -26.5   31.5    0101000020E61000000000000000803F400000000000803AC0      Swaziland       1133066
TD      15      19      0101000020E610000000000000000033400000000000002E40      Chad    9885661
\.
ALTER TABLE ONLY country_centroid
    ADD CONSTRAINT country_idx PRIMARY KEY (country_cde);

Then add a Layer in a stylesheet using the following Datasource:

       <Datasource>
            <Parameter name="dbname">earth</Parameter>
            <Parameter name="estimate_extent">false</Parameter>
            <Parameter name="extent_from_subquery">false</Parameter>
            <Parameter name="st_prefix">true</Parameter>
            <Parameter name="geometry_field">geog</Parameter>
            <Parameter name="host">localhost</Parameter>
            <Parameter name="password">your_secret_password</Parameter>
            <Parameter name="port">5432</Parameter>
            <Parameter name="srid">4326</Parameter>
            <Parameter name="table">country_population_centroid</Parameter>
            <Parameter name="type">postgis</Parameter>
            <Parameter name="user">postgres</Parameter>
            <Parameter name="extent">-180,-90,180,89.99</Parameter>
      </Datasource>

Then add a style with rules like:

        <Rule>
            <MaxScaleDenominator>25000000000</MaxScaleDenominator>
            <MinScaleDenominator>200000000</MinScaleDenominator>
            <TextSymbolizer name="[country_name]" fontset_name="bold-fonts" size="6" fill="#7D7D7D" opacity="0.8" horizontal_alignment="middle" halo_fill="white" halo_radius="2" wrap_width="10" avoid_edges="true"/>
        </Rule>

Render using nik2img

You should see country names rendered in the appropriate locations.

Owner

artemp commented Oct 11, 2011

[Ldp] Would it not be easier to detect the postgis version in use and set st_prefix=true automatically if the postgis version is new enough to support that?

Owner

artemp commented Oct 11, 2011

[rcoup] @ldp: I asked pwicks to file the ticket after working through some issues with him...

I'd like to see:

  • geography type columns detected properly
  • SRIDs/types/extents/etc looked up in the geography_columns table so they don't have to be manually specified
  • ST_ prefixes used automatically depending on PostGIS version (or just see whether we can drop non-ST calls... the prefix has been around for a long while)
Owner

artemp commented Oct 11, 2011

[springmeyer] ya, we should drop non-ST altogether. The only reason I added it as an option is to test performance subtilties between ST_AsBinary and AsBinary as the former internally calls force_2d while the latter does not. But the latter does not support geography type (basically it could but postgis maintainers did not update it because non ST are deprecated).

Owner

artemp commented Oct 11, 2011

[springmeyer] rcoup - are you able to look at this in the next couple of days for release?

Owner

artemp commented Oct 11, 2011

[artem] moving to 2.0.1

Owner

artemp commented Oct 11, 2011

[springmeyer] I'll take a look at this (and generally make a pass on postgis 2.x support)

Owner

springmeyer commented Sep 6, 2014

not critical, casting works

springmeyer closed this Sep 6, 2014

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment