[postgis-users] Spatial index on dynamic view using linear referencing as a geometry

[Lauri Kajan]

Hi PostGIS users,

I'm trying to figure out what is the best method to publish data that's
using linear referencing. I have thought making a view that converts
features using linear referencing to a geometries. Now my question is how I
could use spatial index with my view.

I have a table of 4D linestrings representing roads and then another table
representing features along the roads. Those are using linear referencing.
So features has road_id, starting and ending point on a road.
Now I have created a view to the access the geometry easily so that I can
publish the view from GeoServer. No my question is how to use spatial index
in this case. If I have millions of roads I would like to use spatial index
of the roads table to restrict the view to only those rows. Is there a way
to pass the spatial where clause of the view to the inner sql?

Here is my table structure:
create table roads(
gid int primary key,
geom geometry(linestringzm));
create index roads_geom on roads using gist(geom);

create table features_lr(
fid int primary key,
startPoint real,
endPoint real,
road_id int references roads(gid));

insert into roads values (1, 'LINESTRING(0 0 0 0, 1 0 0 1, 1 1 0 2)');
insert into roads values (2, 'LINESTRING(1 1 0 0, 2 1 0 1, 3 1 0 2, 5 1 0
4)');
insert into features_lr values (1, 0.1, 0.5, 1);
insert into features_lr values (2, 0.5, 3, 2);

create view features_v as
with roads_w as (
  select
    gid,
    st_m(st_startpoint(geom)) as startPoint,
    st_m(st_endpoint(geom)) as endPoint,
    geom
  from roads)
select
  f.fid as fid,
  ST_Line_Substring(r.geom, f.startPoint / (r.startPoint + r.endPoint),
f.endPoint / (r.startPoint + r.endPoint)) as geom
from
  roads_w r
  inner join features_lr f on r.gid = f.road_id;

select fid, st_astext(geom) from features_v where geom && 'BOX(0 0,1
1)'::box2d;


BR,

-Lauri
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/postgis-users/attachments/20130703/f53eac5a/attachment.html>