[mapserver-users] Different projections with MapServer and PostGIS

[Timbonicus Hansen]

Thanks Paul, that did the trick! I knew I had to be missing something
I could do in the mapfile. This makes a lot of sense.

- Tim

On Wed, Dec 2, 2009 at 1:45 PM, Paul Ramsey <pramsey at opengeo.org> wrote:
> In Mapserver, add a PROJECTION block to the LAYER:
>
> PROJECTION
>  "init=epsg:26913"
> END
>
> And also a projection block to the MAP:
>
> PROJECTION
>  "init=epsg:4326"
> END
>
> And then alter the DATA block to declare your data SRID.
>
> DATA "the_geom FROM listing USING UNIQUE listing_id USING srid= 26913"
>
> Now everything will work. Your requests to mapserver will get
> transformed (by mapserver) into the correct projection for your
> postgis data. All the reprojection will happen inside mapserver,
> postgis will be blissfully ignorant of transforms.
>
> P.
>
>
> On Wed, Dec 2, 2009 at 12:34 PM, Timbonicus Hansen
> <timbonicus at realgo.com> wrote:
>> 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?
>> _______________________________________________
>> mapserver-users mailing list
>> mapserver-users at lists.osgeo.org
>> http://lists.osgeo.org/mailman/listinfo/mapserver-users
>>
>