3

I want to know if there's a way to check if a geometry of type Point is inside a geometry of type MultiPolygonZM, ignoring the Z coordinate, I just want to know if my Point(lat, lng) is inside a MultiPolygonZM.

This is an example of a MultiPolygonZM geometry in my database; I used ST_AsText to get a text representation of the geom :

"MULTIPOLYGON ZM (((
570277.5312 3592073.8077 460.900200000004 -1.79769313486232e+308,
570262.0283 3592073.7806 460.712700000004 -1.79769313486232e+308,
570262.4922 3592086.4664 460.705199999997 -1.79769313486232e+308,
570278.0406 3592086.5792 460.697700000004  (...)"

I tried ST_CONTAINS, ST_INTERSECTS like this :

SELECT * FROM CITIES
WHERE
ST_Intersects(ST_SetSRID(geom, 4326), ST_SetSRID(ST_MakePoint(3.67622580, 32.48549230), 4326))

SELECT * FROM CITIES
WHERE
ST_Contains(ST_SetSRID(geom, 4326), ST_SetSRID(ST_MakePoint(3.67622580, 32.48549230), 4326)).

My table uses 4326 as SRID, but i wanted to set it using ST_SetSRID to be sure that all the geoms have the same SRID

But it doesn't work! All the results are false!

Any solution ?

  • Please Edit the question to specify the SRID of the polygon table. You appear to be misusing ST_SetSRID instead of the proper ST_Transform in that context, meaning that dimensionality has nothing to do with the contains failure. In order to use the spatial index you need to transform the point into the polygon table's SRID. – Vince May 11 '17 at 11:05
  • Note that "Point (lat,lon)" reverses proper order (it should be "lon,lat"), which will also scramble topology tests. – Vince May 11 '17 at 12:21
  • I used ST_SetSRID to be sure that all the geoms have the same SRID, and i tried to switch between lat long it doesn't work... Edited – Guebli Mohamed Abdessamed May 11 '17 at 12:47
  • OK, you proplem is that those coordinates are not 4326. So, you need to find out whatever coordinate system that is and use ST_Transform(ST_Setsrid(geom, srid), 4326)) where srid is your actual srid. ST_SetSRID only sets it, it does not do any transforming. You should update your geometry srid, as I suggested in my answer. – John Powell May 11 '17 at 13:10
  • My problem is that my table has SRID 4326 but the coordinate aren't in the same SRID, they are in 32631. So i changed my table's SRDI to 32631 then transform my geom from 32631 to 4326 and it worked! :D Thanks guys – Guebli Mohamed Abdessamed May 11 '17 at 14:37
2

Either ST_Contains or ST_Intersects should work. You can use ST_Force_2D to make a polygon be 2D only.

However, this does not affect the results of an intersect. Note that both of the following, which only differ in a ST_Force2D function, return true (t):

SELECT  
    ST_Intersects(
          ST_Force2d(
               ST_GeomFromText('POLYGON((0 0 2,0 5 2,5 0 2,0 0 2))')),
               ST_MakePoint(2,2));

SELECT  
     ST_Intersects(
            ST_GeomFromText('POLYGON((0 0 2,0 5 2,5 0 2,0 0 2))'),
            ST_MakePoint(2,2));

So, it is possible that your problem lies elsewhere. If you could provide an example of one of your PolygonZM that you think should intersect a point, it would be easier to investigate.

Also, it is generally not a good idea to use ST_SetSRID in queries, but to update the columns using UpdateGeometrySRID. This will have the additional benefit of updating the geometry_columns meta data table, making errors involving different SRIDs impossible and generally keeping your db cleaner and your queries shorter. If you need to use a different SRID, you can always use ST_Transform.

One form, if you exclude the schema name, would be:

SELECT updategeometrysrid('cities', 'geom', 4326);

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.