I would be really grateful to anyone that could help me with the following issue.
I have ever used PostGIS much before – so could be considered a newbie…
My Situation:
I have TWO PostGIS tables:
- roads
- country_boundary
The roads table is so big (within the application 500MB) that I wish to display it by country_boundary.
I can do this successfully using the following query:
select ST_ASBINARY(roads.geog::geometry) from roads, country_boundary
where country_boundary.adm0_name='Sudan'
and ST_Intersects(roads.geog::geometry, country_boundary.geog::geometry)
HOWEVER it takes too long to display the data (several minutes) note: I have tried most optimization techniques, spatial indexing, (ST_Simplify/ST_SimplifyPreserveTopology) these either remove data or takes just as long.
I thought I had found a simple solution that worked – I add a new column 'adm0_name' (aka country name) to the roads table and populated it using the ST_Intersects query above.
update roads
set adm0_name = 'Sudan'
where ST_ASBINARY(roads.geog::geometry) in
(
select ST_ASBINARY(roads.geog::geometry) from roads, country_boundary where country_boundary.adm0_name='Sudan' and ST_Within(roads.geog::geometry, country_boundary.geog::geometry)
);
The resulting display query: works fast, and as expected.. >>problem solved<< ??
select ST_ASBINARY(roads.geog::geometry) from roads where roads.adm0_name='Sudan'
UNFORTUNATELY NO…
The ST_Intersects includes a small number of geometries (polyline(s) that are shared with neighbouring countries. This means polylines (roads) crossing the country boundary, cannot be give a unique country name. Note: I originally used ST_Within but this removed data that was shared between neighbouring countries (as expected with features like roads/rivers that do not respect country boundaries).
….. thanks for bearing with me…
I decided I needed to use ST_Intersection because this would avoid the boundary issue – it would 'clip' the road dataset to each country boundary thus avoiding the polyline boundary issue.
This works – I have an intersection query that displays the data OK
Select ST_ASBINARY (
ST_Intersection(g1.geom1, g1.geom2)) FROM (Select country_boundary.geog::geometry As geom1, roads.geog::geometry As geom2 from country_boundary, roads
where country_boundary.adm0_name = 'Sudan') AS g1;
MY PROBLEM: I cannot find a way to update the roads.adm0_name column with the country name because ST_Intersection seems to returns a geometry, it does not behave like ST_Intersects, where I could update the column.
My attempt to do this is below —> 26 hours later I have reached a point of head scratching. Could anyone of you guys help me out?
update roads
set adm0_name = 'Sudan'
where ST_ASBINARY(roads.geog::geometry) in
(
Select ST_ASBINARY (
ST_Intersection(g1.geom1, g1.geom2)) FROM (Select country_boundary.geog::geometry As geom1, roads.geog::geometry As geom2 from country_boundary, roads where country_boundary.adm0_name = 'Sudan') AS g1;
);
However the above statement fails —> I need to update the roads.adm0_name with country_boundary.adm0_name where (country_boundary "ST_Intersection" roads).
Thanks again —
Best Answer
Thanks for the help.
In the end I made a new table and copied over the columns I needed -- solution below Note: worthy mention to Paul Ramsey who recommended the method below to quicken up the ST_Intersection the query went from 6 hours to 4 minutes. Worth looking up...