[mapserver-users] Different projections with MapServer and PostGIS

[Timbonicus Hansen]

Hi all,

I'm using MapServer to connect to PostGIS and not getting the any
results since the MapServer query and the data in postgresql are
different projections. The 'USING srid' option in the mapfile DATA
field only controls the GeomFromText call that MapServer generates
rather than enforcing a ST_Transform on the database column to match
the projection. Is there any way to make this work?

Here is the PostGIS section from the mapfile I am using:
    CONNECTIONTYPE POSTGIS
    CONNECTION "user=postgres dbname=lqms_prod"
    DATA "the_geom FROM listing USING UNIQUE listing_id USING srid=4326"

Which MapServer munges into this SQL:
    select status,encode(AsBinary(force_collection(force_2d("the_geom")),'NDR'),'base64')
as geom,listing_id from listing where the_geom &&
GeomFromText('POLYGON((-105.074620715937
39.804844079017,-105.074620715937 39.869217095375,-104.927912119243
39.869217095375,-104.927912119243 39.804844079017,-105.074620715937
39.804844079017))',4326);

Unfortunately, the_geom in my database is in EPSG:26913 and the WKT is
in EPSG:4326. It seems the overlap operator (&&) is not smart enough
to pick up on this and convert one or the other - surprisingly,
neither is ST_Overlap. If I modify the query to ST_Transform(the_geom,
4326) && ... results come back as expected (or
ST_Transform(GeomFromText..., 26913)).

The options I see are:
- Change the database column the_geom to use EPSG:4326
- Create a view on the database that translates to EPSG:4326. Does
anyone know if this is feasible or the performance impact?
- Change the passed-in geometry to be EPSG:26913

Is there another option with MapServer? Is there a bug/feature request
to make "USING srid" do a transform on the DB column to match the WKT
conversion?