1

I have a query in PostGIS using st_intersects like this

SELECT "name", "id_punto", "geometry", "properties", "type" FROM "points" AS "points" WHERE st_intersects("geometry",
'POLYGON ((-103.7222993373871 19.24991497165985,-103.71497154235841 19.24991497165985,-103.71497154235841 19.251824273576922,-103.7222993373871 19.251824273576922,-103.7222993373871 19.24991497165985))') = true;

Using st_intersects I bring all the points within a polygon that I indicated everything works well and shows a result like this:

enter image description here

My question is how I can exclude from the result some points within a multipoint for example:

MULTIPOINT (-103.7220613692669 19.25003446877862, -103.7211920807273 19.25105574878461)

try with a leftjoin but I did not get it

2
  • 2
    postgis.net/docs/ST_RemoveRepeatedPoints.html (Returns a version of the given geometry with duplicated points/multi-points removed)
    – Mapperz
    Commented Feb 19, 2019 at 1:14
  • 1
    note that those MultiPoints are not created by that query, but have to be in your source table. do you want to completely remove e.g. all Multipoints? or is @Mapperz suggestion spot on?
    – geozelot
    Commented Feb 19, 2019 at 17:24

1 Answer 1

1

Since PostGIS 2.3.0 you can use ST_Within for a Point-In-Point relationship.

You can add a AND NOT condition to the WHERE clause to exclude them.

The WHERE clause returns the rows for which the condition is true, so you also can establish a condition = false instead of the NOT operator.

Assuming that the geometry column has setted the SRID=4326, both queries are possible:

SELECT
  p.name,  
  p.id_punto,
  p.geometry,
  p.properties,
  p.type
FROM points AS p 
WHERE
  St_Tntersects(
    p.geometry, 
    'SRID=4326; POLYGON(( -103.7222993373871 19.24991497165985, -103.71497154235841 19.24991497165985, -103.71497154235841 19.251824273576922, -103.7222993373871 19.251824273576922, -103.7222993373871 19.24991497165985))'::geometry
  )
  AND NOT
  ST_Within(
    p.geometry, 
    'SRID=4326; MULTIPOINT( -103.7220613692669 19.25003446877862, -103.7211920807273 19.25105574878461)'::geometry
  )
;  

or

SELECT
  p.name,  
  p.id_punto,
  p.geometry,
  p.properties,
  p.type
FROM points AS p 
WHERE
  St_Tntersects(
    p.geometry, 
    'SRID=4326; POLYGON(( -103.7222993373871 19.24991497165985, -103.71497154235841 19.24991497165985, -103.71497154235841 19.251824273576922, -103.7222993373871 19.251824273576922, -103.7222993373871 19.24991497165985))'::geometry
  )
  AND 
  ST_Within(
    p.geometry, 
    'SRID=4326; MULTIPOINT( -103.7220613692669 19.25003446877862, -103.7211920807273 19.25105574878461)'::geometry
  ) = false
;

ST_Within() applied to point-in-point relationship returns true only when the first point geometry is exactly a part of the multipoint geometry. You can use ST_DWithin to exclude them by a distance thereshold.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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