0

I have 4 points that I always get, I would like to query if the polygon defined by a multipoint contains those 4 points. I’m using PostGIS and Postgres.

I'm also using OGR/GDAL for that purpose. Would someone provide me with the Query using SQL for that purpose.

Laurenz Albe
  • 209,280
  • 17
  • 206
  • 263
andre
  • 731
  • 2
  • 13
  • 27

1 Answers1

0

This checks if the points (1 1), (2 2), (3 3), and (4 4) all lie inside the polygon defined by (0 0), (10 0), (10 10), (0 10) and (0 0):

SELECT st_contains(
          st_polygon(
             st_linefrommultipoint(
                st_mpointfromtext(
                   'MULTIPOINT(0 0, 10 0, 10 10, 0 10, 0 0)'
                )
             ),
             0
          ),
          st_mpointfromtext(
             'MULTIPOINT(1 1, 2 2, 3 3, 4 4)'
          )
       );

So to find all multipoints that satisfy the criterion, you could use something like that:

SELECT id
FROM multipoints
WHERE st_contains(
         st_polygon(
            st_addpoint(
               st_linefrommultipoint(
                  multipoints.geom
               ),
               st_startpoint(
                  st_linefrommultipoint(
                     multipoints.geom
                  )
               ),
               -1
            ),
            st_srid(multipoints.geom)
         ),
         st_mpointfromtext(
            'MULTIPOINT(1 1, 2 2, 3 3, 4 4)',
            8307
         )
      );

This assumes that the multipoints don't form a closed polygon (i.e., first point is equal to last).

I used SRID 8307 in my example, replace it with the one you need.

Laurenz Albe
  • 209,280
  • 17
  • 206
  • 263
  • Thanks, I'm beginner, I can get the geometry from the column GEOM. How would I set it in the above SQL statement, I also want to select "All" polygons " from the table not only one polygon – andre Mar 22 '17 at 12:01
  • I have extended the answer with a suggestion. – Laurenz Albe Mar 22 '17 at 12:10
  • FEHLER: Operation on mixed SRID geometries That's what I get. SELECT gid FROM areas WHERE st_contains( areas.geom, st_mpointfromtext( 'MULTIPOINT(16.075 47.950, 16.249 47.914, 16.249 47.91411, 16.232 47.826)' ) ); – andre Mar 22 '17 at 12:16
  • lwpoly_from_lwlines: shell must be closed – andre Mar 22 '17 at 12:23
  • About the SRIDs - well, you must use the same for all. About not closed: the first point is not identical to the last one. – Laurenz Albe Mar 22 '17 at 12:25
  • so the first point of the polygon must be the same as last one ? but I have a "predefined" polygons, which are exactly areas in 2D, how would I make the criteria then ? – andre Mar 22 '17 at 12:27
  • I have edited the answer to match your needs more closely. – Laurenz Albe Mar 22 '17 at 12:53
  • My Polygon points are : x = 16.07514381, double y = 47.95055008; x = 16.24965286, y = 47.91411209; x = 16.23201180, y = 47.82641983; x = 16.14973259, y = 47.79749298; x = 15.93255901, y = 47.89419937; however your query does not return any results, however points 1,1 2,2, 3,3, 4,4 should lie into that polygon – andre Mar 22 '17 at 12:55
  • These points do *not* lie in the polygon. All of the coordinates are greater than 4! – Laurenz Albe Mar 22 '17 at 13:05
  • is it possible to check the reverse, if the polygon lies inside the four points _ – andre Mar 22 '17 at 13:16
  • Again, define what that means. – Laurenz Albe Mar 22 '17 at 13:41
  • I have a problem, I'm using https://www.gaia-gis.it/gaia-sins/spatialite-sql-latest.html and the function St_linefrommultipoint is not available. What can I do – andre Mar 22 '17 at 13:57
  • Use something else that allows you to write SQL. – Laurenz Albe Mar 22 '17 at 14:26
  • I have used the following query which works in my client side, but not sure if its meaning is correct and it's like what you wrote ? – andre Mar 22 '17 at 16:05
  • SELECT * from areas WHERE st_contains(ST_BuildArea(areas.geom),st_mpointfromtext('MULTIPOINT(16.17951 47.85549, 16.17951 47.85549, 16.17951 47.85549, 16.17951 47.85549)',4326)); – andre Mar 22 '17 at 16:05
  • That looks much simpler than my query (I am no PostGIS expert really). You can see if the resulting polygons look right with `st_astext`. You might want to add another answer with your solution for future reference. – Laurenz Albe Mar 22 '17 at 16:47