Re: [postgis-users] Checking Road Network Topology

[toni hernández]

Steve,

I transformed the geometries into a new table.
From there I created a new topology using the new reference system (25831)

On 22/01/2015 16:35, Miller, Stephan wrote:


Thanks.

Here is the code as it now stands.  I was using 4326 and topology will 
not work for geography coordinates.  Can you share your code so that I 
can compare?  I am trying to do an on-the-fly transform to 32648 for 
each record in the line 26.  Is that how you approached this problem?


Thanks,

Steve


*From:*postgis-users-boun...@lists.osgeo.org 
[mailto:postgis-users-boun...@lists.osgeo.org] *On Behalf Of *toni 
hernández

*Sent:* Thursday, January 22, 2015 10:14 AM
*To:* postgis-users@lists.osgeo.org
*Subject:* Re: [postgis-users] Checking Road Network Topology

Steve,

I had the same issue when working with geometries using srid=4326 . I 
was also following the same link 
(http://blog.mathieu-leplatre.info/use-postgis-topologies-to-clean-up-road-networks.html)
Then I tried with some projected geometries (units = meters) and the 
problem was gone. I am not sure why. Maybe it was just a coincidence.


cheers.

On 20/01/2015 20:45, Miller, Stephan wrote:

All –

I am attempting to validate the topology of a dense road network (
1.2 Million roads) modeled after

http://blog.mathieu-leplatre.info/use-postgis-topologies-to-clean-up-road-networks.html
from July 2013
. 
The code is given below:


-- HC_RoadTopologicalCheck

-- Drop the existing topology

SELECT topology.DropTopology('roads_topo');

SELECT topology.CreateTopology('roads_topo',4326, 0.01, true);

SELECT topology.AddTopoGeometryColumn('roads_topo', 'fgcm',
'transportationgroundcrv', 'topo_shape', 'LINESTRING');

--UPDATE transportationgroundcrv SET topo_shape =
topology.toTopoGeom(transportationgroundcrv.shape, 'roads_topo',
1, 1.0);

-- Encapsulate the UPDATE inside code that checks for errors

DO $$ DECLARE r record;

BEGIN

FOR r IN SELECT * FROM transportationgroundcrv loop

BEGIN

UPDATE transportationgroundcrv SET topo_shape =
topology.toTopoGeom(transportationgroundcrv.shape, 'roads_topo',
1, 1.0)

WHERE r.objectid = r.objectid;

EXCEPTION

WHEN OTHERS THEN

RAISE WARNING 'Loading of record % failed: %',
r.objectid, SQLERRM;

END;

END LOOP;

END$$;

Select * from ValidateTopology('roads_topo');

I am getting everything passing to the exception statement and all
the records are being flagged with

WARNING:  Loading of record 791456 failed: SQL/MM Spatial
exception - curve not simple

Intermittently, I am getting the following error also.

WARNING:  Loading of record 792591 failed: Spatial exception -
geometry intersects edge 69612

Everything appears to be flagged as an error; the ValidateTopology
call returns nothing.

Any insights or suggestions would be appreciated.

Steve




___

postgis-users mailing list

postgis-users@lists.osgeo.org  

http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users



___
postgis-users mailing list
postgis-users@lists.osgeo.org
http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users


___
postgis-users mailing list
postgis-users@lists.osgeo.org
http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users

Re: [postgis-users] Possible for ST_Intersects to not include the perimeter?

[Ben Madin]

For two cents, a quick but inaccurate solution might be to shrink your
polygon slightly by using st_buffer() with a negative radius?

Ben

On 22 January 2015 at 16:50, toni hernández  wrote:

>  Joseph,
>
> With St_Relate (http://postgis.net/docs/ST_Relate.html) we can get all
> kind of relations between geometries.
> You can get boundary intersections, interior intersections, interior
> intersections without boundary intersections all possibilities are
> supported.
>
> St_Relate uses the DE-91M model to define this relations between
> geometries.
> To know more about DE-91M: http://en.wikipedia.org/wiki/DE-9IM
>
>
>
> http://postgis.net/docs/using_postgis_dbmanagement.html#DE-9IM
>
>
> On 21/01/2015 22:22, Joseph Spenner wrote:
>
> I have a polygon as an input, and I'm trying to find all polygons which
> share points, but not the perimiter points themselves.  Is this possible?
>
>  Real application:
>   I have NWS polygons describing Thunderstorm Warnings, which are made up
> of counties within the state.  Some of those counties might be on the edge
> of the state.  I want to query my database to show me all Warnings which
> are in a supplied state.  However, if I query the *adjacent* state which
> borders the counties from the first state, I get those Warnings because
> they share the same parimeter points.
>
>  Is there a way to construct a query which will not return anything if
> the only points in common are the perimeter values themselves?
>
>  I tried a few variations on
> ST_Intersects/ST_Covers/ST_Contains/ST_Overlaps, supplying various AND/OR
> and TRUE/FALSE combinatins, but I can't seem to get the right combo to
> accomplish my task.
>
>  Any help would be great.
>
>  Thanks!
>
>  Regards,
> Joseph Spenner
>
>
>
>
> ___
> postgis-users mailing 
> listpostgis-users@lists.osgeo.orghttp://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users
>
>
>
> ___
> postgis-users mailing list
> postgis-users@lists.osgeo.org
> http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users
>



-- 

Ben Madin
BVMS PhD, MANZCVS

t : +61 8 6102 5535
m : +61 448 887 220
e : b...@ausvet.com.au

AusVet Animal Health Services
Western Australia

www.ausvet.com.au
___
postgis-users mailing list
postgis-users@lists.osgeo.org
http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users

Re: [postgis-users] Checking Road Network Topology

[Miller, Stephan]

Thanks.

I think I am ensuring the geometries are both projected to 32648 (zone 48N  
UTM).  The tolerance being sent to topology.toTopoGeom is 1.0, presumably this 
is in meters.  I will try the next run with 0.001 which is appropriate if 
the tolerance is being applied to 4326 coordinates for some reason.  A 
tolerance of 1.0 degree would certainly explain why every line would be reduced 
to a point or convolute the input.

I have not gotten around to using LIMIT and OFFSET as you suggested.  I keep 
thinking I have overlooked something important.

Here is the code I am running now.



-- Drop the existing topology
SELECT topology.DropTopology('roads_topo');

-- Create a new one
SELECT topology.CreateTopology('roads_topo',32648, 0.01, true);

-- Add the new topo_shape column
select topology.AddTopoGeometryColumn('roads_topo', 'fgcm', 
'transportationgroundcrv', 'topo_shape', 'LINESTRING');

SELECT TopologySummary('roads_topo');

-- Encapsulate the UPDATE inside code that checks for errors
DO $$DECLARE r record;
BEGIN
FOR r IN SELECT objectid, f_code, shape, topo_shape FROM 
transportationgroundcrv loop
BEGIN
  UPDATE transportationgroundcrv SET topo_shape = 
topology.toTopoGeom(ST_Transform((ST_SetSRID(shape,4326)),32648), 'roads_topo', 
1, 1.0);
EXCEPTION
WHEN OTHERS THEN
RAISE WARNING 'Loading of record % failed: % %', r.objectid, 
SQLSTATE, SQLERRM;
END;
END LOOP;

END$$;

Select * from ValidateTopology('roads_topo');



Let me know if you see something obvious.

Steve
 

-Original Message-
From: postgis-users-boun...@lists.osgeo.org 
[mailto:postgis-users-boun...@lists.osgeo.org] On Behalf Of Sandro Santilli
Sent: Thursday, January 22, 2015 3:53 PM
To: PostGIS Users Discussion
Subject: Re: [postgis-users] Checking Road Network Topology

On Thu, Jan 22, 2015 at 04:13:44PM +0100, toni hernández wrote:
> Steve,
> 
> I had the same issue when working with geometries using srid=4326 .
> I was also following the same link 
> (http://blog.mathieu-leplatre.info/use-postgis-topologies-to-clean-up-
> road-networks.html) Then I tried with some projected geometries (units 
> = meters) and the problem was gone. I am not sure why. Maybe it was 
> just a coincidence.

Can it be you're specifying a too large of a precision for your topologies in 
srid=4326 ?

--strk;
___
postgis-users mailing list
postgis-users@lists.osgeo.org
http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users

___
postgis-users mailing list
postgis-users@lists.osgeo.org
http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users

Re: [postgis-users] Checking Road Network Topology

[Sandro Santilli]

On Thu, Jan 22, 2015 at 04:13:44PM +0100, toni hernández wrote:
> Steve,
> 
> I had the same issue when working with geometries using srid=4326 .
> I was also following the same link 
> (http://blog.mathieu-leplatre.info/use-postgis-topologies-to-clean-up-road-networks.html)
> Then I tried with some projected geometries (units = meters) and the
> problem was gone. I am not sure why. Maybe it was just a
> coincidence.

Can it be you're specifying a too large of a precision for your
topologies in srid=4326 ?

--strk;
___
postgis-users mailing list
postgis-users@lists.osgeo.org
http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users

Re: [postgis-users] Possible for ST_Intersects to not include the perimeter?

[Joseph Spenner]

Thanks for all the replies!  Actually, I must have done something wrong 
yesterday because my query I came up with works:
select ST_Asgeojson( geom ) from polys where 
ST_Intersects(ST_GeomFromGeoJSON('$jsonPoly'), geom)=TRUE and 
ST_Touches(ST_GeomFromGeoJSON('$jsonPoly'), geom)=FALSE
I must have had a TRUE/FALSE  and/or  AND/OR wrong.
I went through several tests, and all scenarios where the polys lie in various 
places with respect to each other, and every test past.
Toni:  The ST_Relate looks interesting, too.  I'll have to check that out.
Thanks again for the quick replies!
 If life gives you lemons, keep them-- because hey.. free lemons.


"~heart~ Sticker"  fixer:  http://microflush.org/stuff/stickers/heartFix.html



  From: toni hernández 
 To: Joseph Spenner ; PostGIS Users Discussion 
 
 Sent: Thursday, January 22, 2015 1:50 AM
 Subject: Re: [postgis-users] Possible for ST_Intersects to not include the 
perimeter?
   
 Joseph,
 
 With St_Relate (http://postgis.net/docs/ST_Relate.html) we can get all kind of 
relations between geometries. 
 You can get boundary intersections, interior intersections, interior 
intersections without boundary intersections all possibilities are 
supported.
 
 St_Relate uses the DE-91M model to define this relations between geometries.
 To know more about DE-91M: http://en.wikipedia.org/wiki/DE-9IM
 
 
 
 http://postgis.net/docs/using_postgis_dbmanagement.html#DE-9IM
 
 

On 21/01/2015 22:22, Joseph Spenner wrote:
  
 I have a polygon as an input, and I'm trying to find all polygons which share 
points, but not the perimiter points themselves.  Is this possible? 
  Real application:   I have NWS polygons describing Thunderstorm Warnings, 
which are made up of counties within the state.  Some of those counties might 
be on the edge of the state.  I want to query my database to show me all 
Warnings which are in a supplied state.  However, if I query the adjacent state 
which borders the counties from the first state, I get those Warnings because 
they share the same parimeter points. 
  Is there a way to construct a query which will not return anything if the 
only points in common are the perimeter values themselves? 
  I tried a few variations on ST_Intersects/ST_Covers/ST_Contains/ST_Overlaps, 
supplying various AND/OR and TRUE/FALSE combinatins, but I can't seem to get 
the right combo to accomplish my task. 
  Any help would be great. 
  Thanks! 
  Regards, Joseph Spenner
  
  

  
 ___
postgis-users mailing list
postgis-users@lists.osgeo.org
http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users 
 
 

  ___
postgis-users mailing list
postgis-users@lists.osgeo.org
http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users

Re: [postgis-users] Checking Road Network Topology

[toni hernández]

Steve,

I had the same issue when working with geometries using srid=4326 . I 
was also following the same link 
(http://blog.mathieu-leplatre.info/use-postgis-topologies-to-clean-up-road-networks.html)
Then I tried with some projected geometries (units = meters) and the 
problem was gone. I am not sure why. Maybe it was just a coincidence.


cheers.


On 20/01/2015 20:45, Miller, Stephan wrote:


All –

I am attempting to validate the topology of a dense road network ( 1.2 
Million roads) modeled after 
http://blog.mathieu-leplatre.info/use-postgis-topologies-to-clean-up-road-networks.html 
from July 2013 
.  
The code is given below:


-- HC_RoadTopologicalCheck

-- Drop the existing topology

SELECT topology.DropTopology('roads_topo');

SELECT topology.CreateTopology('roads_topo',4326, 0.01, true);

SELECT topology.AddTopoGeometryColumn('roads_topo', 'fgcm', 
'transportationgroundcrv', 'topo_shape', 'LINESTRING');


--UPDATE transportationgroundcrv SET topo_shape = 
topology.toTopoGeom(transportationgroundcrv.shape, 'roads_topo', 1, 1.0);


-- Encapsulate the UPDATE inside code that checks for errors

DO $$ DECLARE r record;

BEGIN

FOR r IN SELECT * FROM transportationgroundcrv loop

BEGIN

UPDATE transportationgroundcrv SET topo_shape = 
topology.toTopoGeom(transportationgroundcrv.shape, 'roads_topo', 1, 1.0)


WHERE r.objectid = r.objectid;

EXCEPTION

WHEN OTHERS THEN

RAISE WARNING 'Loading of record % failed: %', 
r.objectid, SQLERRM;


END;

END LOOP;

END$$;

Select * from ValidateTopology('roads_topo');

I am getting everything passing to the exception statement and all the 
records are being flagged with


WARNING:  Loading of record 791456 failed: SQL/MM Spatial exception - 
curve not simple


Intermittently, I am getting the following error also.

WARNING:  Loading of record 792591 failed: Spatial exception - 
geometry intersects edge 69612


Everything appears to be flagged as an error; the ValidateTopology 
call returns nothing.


Any insights or suggestions would be appreciated.

Steve



___
postgis-users mailing list
postgis-users@lists.osgeo.org
http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users


___
postgis-users mailing list
postgis-users@lists.osgeo.org
http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users

Re: [postgis-users] Checking Road Network Topology

[Rémi Cura]

Wow this seems like really impressive work, at a very cool scale!
It is more or less the kind of tool I dreamt to have.

Processing a 1.2 million topology is really not easy,
I wonder if CGAL implementation is multiprocessed.

Cheers,
Rémi-C

2015-01-22 8:00 GMT+01:00 Paolo Cavallini :

> Il 22/01/2015 01:28, Mark Wynter ha scritto:
>
> > I’m open to any ideas, leads or offers of help.
>
> I might be able to help, especially on the QGIS side; perhaps setting up
> Processing modules could make the workflow easier.
> All the best.
>
> --
> Paolo Cavallini - www.faunalia.eu
> QGIS & PostGIS courses: http://www.faunalia.eu/training.html
> ___
> postgis-users mailing list
> postgis-users@lists.osgeo.org
> http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users
>
___
postgis-users mailing list
postgis-users@lists.osgeo.org
http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users

Re: [postgis-users] Possible for ST_Intersects to not include the perimeter?

[toni hernández]

Joseph,

With St_Relate (http://postgis.net/docs/ST_Relate.html) we can get all 
kind of relations between geometries.
You can get boundary intersections, interior intersections, interior 
intersections without boundary intersections all possibilities are 
supported.


St_Relate uses the DE-91M model to define this relations between geometries.
To know more about DE-91M: http://en.wikipedia.org/wiki/DE-9IM



http://postgis.net/docs/using_postgis_dbmanagement.html#DE-9IM

On 21/01/2015 22:22, Joseph Spenner wrote:
I have a polygon as an input, and I'm trying to find all polygons 
which share points, but not the perimiter points themselves.  Is this 
possible?


Real application:
  I have NWS polygons describing Thunderstorm Warnings, which are made 
up of counties within the state.  Some of those counties might be on 
the edge of the state.  I want to query my database to show me all 
Warnings which are in a supplied state. However, if I query the 
/adjacent/ state which borders the counties from the first state, I 
get those Warnings because they share the same parimeter points.


Is there a way to construct a query which will not return anything if 
the only points in common are the perimeter values themselves?


I tried a few variations on 
ST_Intersects/ST_Covers/ST_Contains/ST_Overlaps, supplying various 
AND/OR and TRUE/FALSE combinatins, but I can't seem to get the right 
combo to accomplish my task.


Any help would be great.

Thanks!

Regards,
Joseph Spenner




___
postgis-users mailing list
postgis-users@lists.osgeo.org
http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users


___
postgis-users mailing list
postgis-users@lists.osgeo.org
http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users