User:⠠⠁⠝⠞⠓⠕⠝⠽/SQL

From OpenStreetMap Wiki
Jump to: navigation, search

Warning: Uses the "window function" rank(), which means Postgresql 8.4 or higher.

create or replace view neighborhood_parcel as
 select * from parcel where nbhc='212005.00000' and pin like '%-000009-%';

create or replace view outside_borders as select a.gid, a.site_addr,
 ST_Difference(ST_Boundary(a.the_geom), ST_Boundary(ST_Union(b.the_geom))) as the_geom
 from neighborhood_parcel as a join neighborhood_parcel as b on a.gid!=b.gid
 where ST_Intersects(a.the_geom, b.the_geom) group by a.gid, a.site_addr, a.the_geom;

create or replace view inside_borders as select a.gid as gid, a.site_addr as site_addr,
 b.gid as ogid, b.site_addr as osite_addr, ST_Intersection(a.the_geom, b.the_geom) as the_geom
 from neighborhood_parcel as a join neighborhood_parcel as b on a.gid!=b.gid
 where ST_Touches(a.the_geom, b.the_geom) and ST_Dimension(ST_Intersection(a.the_geom, b.the_geom))=1;

create or replace view linestring_ways as
 select gid, site_addr, ST_GeometryN(the_geom,generate_series(1, ST_NumGeometries(the_geom))) as the_geom
  from inside_borders
  where GeometryType(the_geom)='MULTILINESTRING'
 union select gid, site_addr, the_geom
  from inside_borders
  where GeometryType(the_geom)='LINESTRING'
 union select gid, site_addr, ST_GeometryN(the_geom,generate_series(1, ST_NumGeometries(the_geom))) as the_geom
  from outside_borders
  where GeometryType(the_geom)='MULTILINESTRING'
 union select gid, site_addr, the_geom
  from outside_borders
  where GeometryType(the_geom)='LINESTRING';

create or replace view ordered_ways as
 select gid, site_addr, rank() over (order by the_geom) as way_id, the_geom
 from linestring_ways;

create or replace view relation_members as
 select rank() over (order by gid) as relation_id, gid, site_addr, way_id
 from ordered_ways;

create or replace view distinct_ways as
 select distinct way_id, ST_SimplifyPreserveTopology(the_geom, 0.00001) as the_geom
 from ordered_ways;

create or replace view way_nodes as
 select way_id, c, rank() over (order by ST_AsText(ST_PointN(the_geom,c))) as node_id, ST_PointN(the_geom,c)
 from (
  select way_id,the_geom,generate_series(1, ST_NPoints(the_geom)) as c
  from distinct_ways
 ) as foo order by way_id, c;

create or replace view all_nodes as
 select distinct node_id, ST_X(st_pointn)as lon, ST_Y(st_pointn) as lat
 from way_nodes
 order by node_id;

Also see User:⠠⠁⠝⠞⠓⠕⠝⠽/Perl