1

I'm trying to find out the lat/lon of points on a linestring that are not contained inside a geometry formed by a ST_buffer. I can find if the linestring intersects the geometry but I'm not able to find how can I get the points.

For example. Consider the following image. The substrings of the linestring that are not covered by the polygon are BC and FG. So, I'm trying to get the lat/lon of the B,C,F,G in order. Is it possible to do this in PostGIS ?

UPDATE:

I've written this query so far but it returns more than expected values. Some of them are inside the ST_buffer.

SELECT ST_AsText(ST_Difference(road::geometry,circle.the_geog::geometry)) FROM (SELECT ST_GeographyFromText('SRID=4326;LINESTRING(85.269150 23.157234,85.278541 23.101098,85.286367 23.047280,85.287737 23.006227)') as road) AS road,towers as circle WHERE ST_Intersects( circle.the_geog,ST_GeographyFromText('SRID=4326;LINESTRING(85.269150 23.157234,85.278541 23.101098,85.286367 23.047280,85.287737 23.006227)')
    );

UPDATE 2:

I also tried ST_SymDifference. Though the results are correct but I'm also getting POLYGON in the GEOMETRYCOLLECTION. Also it returns many rows instead of just one.

SELECT ST_AsText(ST_SymDifference(road::geometry,circle.the_geog::geometry)) 
FROM (SELECT ST_GeographyFromText('SRID=4326;LINESTRING(85.269150 23.157234,85.278541 23.101098,85.286367 23.047280,85.287737 23.006227)') as road) 
AS road,poly as circle WHERE ST_Intersects(circle.the_geog,road);

enter image description here

2

ST_Split will split a line by a multi polygon and return a geometry collection by which you can retreive the lat,long of the start and endpoints.

This statement:

  1. uses some similiar test geometry of 1 multiline and 3 polygon circles:
  2. combines the 3 circles into a single geometry with Union All and ST_Union
  3. gets the intersected geometry between the line and the combined circles with ST_Intersection
  4. simplifies the intersected geometry down to the endpoints with ST_Simplify
  5. dumps the simplified points with ST_Dumppoints
  6. and finally displays each points Lat,Long with ST_X and ST_Y

SELECT (t.d).path,ST_X((t.d).geom),ST_Y((t.d).geom) FROM
 (SELECT ST_DumpPoints(
        ST_Simplify(
        ST_Intersection(ST_GeomFromEWKT('LineString (85.26914999999999623 23.15723399999999899, 85.27854100000000415 23.10109800000000035, 85.27991034974166951 23.0916812271406684, 85.28636699999999848 23.04728000000000065, 85.28773700000000701 23.00622699999999909)'),
(SELECT ST_Union(geom )
FROM
(SELECT ST_GeomFromEWKT('POLYGON((85.27915 23.157234,85.2762210678119 23.1501629321881,85.26915 23.147234,85.2620789321881 23.1501629321881,85.25915 23.157234,85.2620789321881 23.1643050678119,85.26915 23.167234,85.2762210678119 23.1643050678119,85.27915 23.157234))') as geom
        UNION ALL
SELECT ST_GeomFromEWKT('POLYGON((85.288541 23.101098,85.2856120678119 23.0940269321881,85.278541 23.091098,85.2714699321881 23.0940269321881,85.268541 23.101098,85.2714699321881 23.1081690678119,85.278541 23.111098,85.2856120678119 23.1081690678119,85.288541 23.101098))') as geom
        UNION ALL
SELECT ST_GeomFromEWKT('POLYGON((85.29 23.09,85.2870710678119 23.0829289321881,85.28 23.08,85.2729289321881 23.0829289321881,85.27 23.09,85.2729289321881 23.0970710678119,85.28 23.1,85.2870710678119 23.0970710678119,85.29 23.09))') as geom ) as three_circles)),3)) as d) as t
  • ST_Split will also split the other circles that are not required. For example the two circles in the figure. – Rahul Jun 22 '16 at 20:30
  • Updated with new answer – kttii Jun 23 '16 at 14:34

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.