[MAPSERVER-USERS] [Postgis] Intersection on requested data

[G. Tiemens]

Paul Ramsey-3 wrote:
> 
> You need to understand the syntax of the DATA statement, which is not
> pure SQL. It is:
> 
> [columntodraw] FROM [sourceofdata] USING UNIQUE [uniquekey] USING
> SRID=[srid]
> 
> columntodraw is always assumed to be a single word.
> 
> Write your data statement like this:
> 
> DATA  "the_geom FROM (SELECT gid,ST_Intersection(kl_l.the_geom ,
> ST_GeomFromText('POLYGON((165607 387820 , 165651  387813 , 165672
> 387759 , 165598  387800 , 165607  387820))' , 28992)) AS the_geom FROM
> kl_l) AS SUBQUERY USING UNIQUE gid USING SRID=28992"
> 
> Note that result of the intersection is aliased as "the_geom" so that
> it matches the columntodraw cited at the front, and the unique id is
> included in the subquery and cited in the using unique. Also the SRID
> is cited.
> 
> P.
> _______________________________________________
> mapserver-users mailing list
> mapserver-users at lists.osgeo.org
> http://lists.osgeo.org/mailman/listinfo/mapserver-users
> 
> 

Many thanks... after several trying I was already close to the answer. At
first, i substituted the "geomfromtext"  with a geometry from another
postgis table. This, to prevent the error being the quotes inside the
"geomfromtext" . These quotes costed me a lot of errors.

Afterwards the DATA string i got was really close to what i needed. But the
error coming back was:   can't find the srid in the geometry_columns table.
Problem was that the Intersection() as the_geom  caused that the_geom became
ambigious (since it was already defined in the kl_l table.)
Renaming the intersection caused that mapserver requested for a non-existing
tablegeometry in the geometry_columns (findsrid("intersectionalias"))

To solve the problem, the specific needed columns of the kl_l table had to
be defined in the request and the intersection alias must be the same as the
geometry column of kl_l

So here I ended up with the final string that worked well for me:

DATA "the_geom FROM (SELECT ST_Intersection(kl_l.the_geom,
ST_GeomFromText('POLYGON((165607 387820 , 165651  387813 , 165672 387759 ,
165598  387800 , 165607  387820))' , 28992)) AS the_geom , kl_l.class,
kl_l.label, kl_l.gid FROM kl_l) AS subquery USING UNIQUE gid USING SRID =
28992"

I hope this will be informational for others looking for the same problem.

Greetings
Gerben

-- 
View this message in context: http://www.nabble.com/-Postgis--Intersection-on-requested-data-tp20229230p20269397.html
Sent from the Mapserver - User mailing list archive at Nabble.com.