PostGIS geometry database query

Question

I have several tables that contain multipolygons. I need to find points within these polygons that I can use in my java test class. What I have been doing is sending a query to return all the multi polygons, choose a vertex to use as a point, and most times it works.

However these tables represent risk data, 1 in 100, 1 in 200 etc, and so some of the points are shared between tables (the higher risk multi polygons are encapsulated by the lower risk). what query can I use to return a point that will be within 1 multipolygon in 1 table, but not in any others that I specify?

the tables are river_100_1k, river_200_1k, and river_1000_1k

score 1 · Accepted Answer · answered Mar 07 '12 at 20:24

Well you could do a multiple left join:

SELECT a.gid, a.the_geom FROM pointsTable a
LEFT JOIN river_100_1k b 
ON ST_Intersects(a.the_geom, b.the_geom)
LEFT JOIN 
river_200_1k c
ON NOT ST_Intersects(a.the_geom, c.the_geom) -- Not Intersects
LEFT JOIN
river_1000_1k d 
ON NOT ST_Intersects(a.the_geom, d.the_geom) -- Not Intersects
WHERE 
AND c.gid IS NULL AND d.gid IS NULL AND b.gid=2 AND c.gid=2 AND d.gid=2 ;

I'm not sure if I understand correctly but this is the path you should take.

score 0 · Answer 2 · edited Dec 23 '14 at 16:16

0

Use ST_PointOnSurface(polygon) to get a point within a polygon.

edited Dec 23 '14 at 16:16

bluish

26,356
27
122
180

answered Mar 08 '12 at 16:11

Paul Ramsey

839
6
8

PostGIS geometry database query

2 Answers2