Vascular Surgery (on road networks)

[Seldom]

I've just come across some well aligned Forest Service road data (with road numbers) that I'd like to substitute for Tiger "residential roads" in my map. 

My workflow at the moment is to download the boundary polygon for a Forest area, use GM to select all roads in my original map that are completely within the polygon, and delete them.  Then I add the roads from my new dataset.

My problem is that I have to trim and re-attach polylines all around the perimeter of the polygon when roads only lie partially within the polygon.  I also have to load imagery often to figure out which of two adjacent roads are correct.

If anyone can suggest an easier way, I'd love to hear it.

[maps4gps]

So would I. 
There never seams to be an easy way to merge data from two (or more) sources.

[leszekp]

A GIS program (like the free MapWindow program, http://mapwindow.org) might be able to help you with this. It has a tool that lets you clip any shapefile with another arbitrary polygon shapefile. You can also load in a raster map layer as the base, then load in multiple vector shapefiles on top to compare, and delete features that you don't need.

[Seldom]

Thanks, leszekp.  I'll take a look at MapWindow, but I'm pretty sure Global Mapper includes all those functions, including raster underlays. 

My biggest problem is the time to load raster imagery from online sources.  A 10 second trim operation takes 1 or 2 minutes to get necessary imagery.

[Seldom]

Trying to automate my road network insertion using PostGIS.  I've got a set of Forest Service roads "FS_Inside", surrounded by Tiger Shapefile roads "TigerOutside".  The query locates all "FS_Inside" startpoints that are within 30m of "TigerOutside" startpoints, and lists the "distance" between them (only if the distance is less than 30m).  The goal of the last two lines is to  edit the "FS_Inside" startpoint to match the "TigerOutside" startpoint, but there lies the rub.  Got it fully stitched together with the following query:

Code Select Expand

SELECT
  *
FROM
  public."FS_Inside",
  public."TigerOutside"
WHERE
"FS_Inside".gid = "TigerOutside".gid;
ALTER TABLE "TigerOutside" ADD COLUMN tigerstartpoint geometry;
ALTER TABLE "TigerOutside" ADD COLUMN tigerendpoint geometry;
ALTER TABLE "FS_Inside" ADD COLUMN TigerStartpoint geometry;
ALTER TABLE "FS_Inside" ADD COLUMN startpoint geometry;
ALTER TABLE "FS_Inside" ADD COLUMN endpoint geometry;
ALTER TABLE "FS_Inside" ADD COLUMN tigerendpoint geometry;
ALTER TABLE "FS_Inside" ADD COLUMN new_line geometry;
ALTER TABLE "FS_Inside" ADD COLUMN numpoints integer;
ALTER TABLE "FS_Inside" ADD COLUMN distance real;
SELECT
  *
FROM
  public."FS_Inside",
  public."TigerOutside"
WHERE
"FS_Inside".gid = "TigerOutside".gid;

UPDATE "TigerOutside" SET tigerstartpoint=ST_Startpoint(the_geom);
UPDATE "TigerOutside" SET tigerendpoint=ST_Endpoint(the_geom);
UPDATE "FS_Inside" SET new_line=ST_Linemerge(the_geom);
UPDATE "FS_Inside" SET numpoints=ST_NumPoints(new_line)-1;
UPDATE "FS_Inside" SET startpoint=ST_Startpoint(the_geom);
UPDATE "FS_Inside" SET endpoint=ST_endpoint(the_geom);
UPDATE "FS_Inside" SET TigerStartpoint="TigerOutside".tigerstartpoint FROM "TigerOutside";
UPDATE "FS_Inside" SET TigerEndPoint="TigerOutside".tigerendpoint FROM "TigerOutside";
SELECT
  *
FROM
  public."FS_Inside",
  public."TigerOutside"
WHERE
  ST_Distance("FS_Inside".startpoint,"TigerOutside".tigerstartpoint)<30 ;
UPDATE "FS_Inside" SET new_line= ST_SetPoint(new_line,0,"TigerOutside".tigerstartpoint) FROM "TigerOutside"
WHERE
  ST_Distance("FS_Inside".startpoint,"TigerOutside".tigerstartpoint)<30 ;

  SELECT
  *
FROM
  public."FS_Inside",
  public."TigerOutside"
WHERE
  ST_Distance("FS_Inside".startpoint,"TigerOutside".tigerendpoint)<30 ;
UPDATE "FS_Inside" SET new_line= ST_SetPoint(new_line,0,"TigerOutside".tigerendpoint) FROM "TigerOutside"
WHERE
  ST_Distance("FS_Inside".startpoint,"TigerOutside".tigerendpoint)<30 ;

    SELECT
  *
FROM
  public."FS_Inside",
  public."TigerOutside"
WHERE
  ST_Distance("FS_Inside".endpoint,"TigerOutside".tigerstartpoint)<30 ;
UPDATE "FS_Inside" SET new_line= ST_SetPoint(new_line,ST_NumPoints(new_line)-1,"TigerOutside".tigerstartpoint) FROM "TigerOutside"
WHERE
  ST_Distance("FS_Inside".endpoint,"TigerOutside".tigerstartpoint)<30 ;
 
  SELECT
  *
FROM
  public."FS_Inside",
  public."TigerOutside"
WHERE
  ST_Distance("FS_Inside".endpoint,"TigerOutside".tigerendpoint)<30 ;
UPDATE "FS_Inside" SET new_line= ST_SetPoint(new_line,ST_NumPoints(new_line)-1,"TigerOutside".tigerendpoint) FROM "TigerOutside"
WHERE
  ST_Distance("FS_Inside".endpoint,"TigerOutside".tigerendpoint)<30 ;


This PostGreSQL is neat stuff!  The sample I used had 4505 records in one table and 3989 records in the other table, and it took about half an hour to run. 

However, now that I've got the ends stitched together (they route in GPSmapedit), I'm still getting a lot of routing errors when I run "Verify Map" so I'm looking for a way to reduce the number of vertices on lines.  So far I've been running a "simplify" command in GM.  It seems there should be a way to use ST_RemovePoint() to do it in PostGreSQL, but that only removes one point at a time and I don't see a way to generate a series.

[maps4gps]

Removing coordinate pairs can result in a more general scale map (for those features).

[Seldom]

Removing coordinate pairs can result in a more general scale map (for those features).

It would be nice to find a PostGIS equivalent to "Generalize" in GPSmapedit.  Haven't figured out how to use them yet, but one of the ST_Simplify() functions should do it.

[Seldom]

ST_SimplifyPreserveTopology() did a nice job of reducing points.  It also reduced my GPSmapEdit "Verify Map" errors from around 500 to 80.  Now if I can split all the closed loops, I should have something really useful, because I should be able to generate routable maps from Tiger instead of relying on OSM. 

I can query with ST_IsClosed() to find all the loops,  and ST_LineSubString() should be let me generate the split segments, But I'm still trying to figure a way to manage records.  I'd like to make a copy of all the attribute data except the modified geometry, modify the geometry in the first half then modify the geometry in the second half.  The INSERT INTO tutorial only shows how to create records by adding VALUES.

[Seldom]

I now have a kludge working, but the only way I've found to add records for the split loop halves is to use INSERT INTO to create records in a second table,  Then re-insert those records into my original table and delete all the closed loops.

All my efforts at inserting records from Table "A" into Table "A" generate error messages.

[Seldom]

I've run into another problem.  All the self intersecting roads that GPSmapedit finds aren't "closed" where the startpoint = the endpoint.  An irritating few are lassos where an endpoint attaches to a point midway down a line.  Neither PostGIS nor Global Mapper recognize these as "closed", and PostGIS reports them as "simple".  Since I can't detect the lassos I can't come up with a procedure to split them.

[Seldom]

Still more problems:  Anybody know a way to tell if PostGreSQL/PostGIS has crashed?  So far the best I can to is wait 3 or 4 hours, try to close the SQL script window, and if it's unresponsive, figure it's died.

At the moment I'm querying a table with 40,000 records.  The query creates a point at ST_ClosestPoint(foo) where ST_Distance(St_Endpoint(fee),ST_ClosestPoint(foo))< 5 meters.  The query works fine on a table with 5000 records, but runs forever with 40,000 records so I assume I'm running out of memory.  PostGIS is a 32 bit application, so that limits everything to 3.2GB.  I've got no idea how to set PostGreSQL to use the maximum allowable.

[maps4gps]

In Windows: ctrl/alt/del will open a ?? window - then chose start task manager

In tast manager chose performance - it will show the cpu usage and the memory usage

You might then chose resource monitor - then disk and/or memory to see a second by second list of what is changing.

[Seldom]

Thanks, maps4gps.   I've got the Resource Monitor and I see 11 postgres.exe processes and a pgadmin process. From time to time the commit, working set, shareable, and private numbers change.  Does that mean the process is running, or could "the lights be on with nobody home"?

[maps4gps]

I do not know.
Comparing how these are changing now with how they were changing when first started might have some meaning; however, there is the question of  it might be 10 minitues from finishing or many hours/days.  Hate to stop it and later find it was just a few mintues from finishing. 
Any way to break the table into parts of 5,000 or 10,000 or 20,000?

GlobalMapper seams to work very fast with small files - probably everything in RAM cache;
slow with larger files - likely the indexes in RAM cahce and the data moved to and from the hard drive as needed; then terminates processing with larger files  - likely the indexes have exceded the space alloted/available for the indexes.  A guess, but seams reasonable to me.

[Seldom]

This is still a work in progress, but the query completed after I deleted half the records in the table .