Spatial Query

[former_SQLA_member1694898]

We have an application that utilized the SQL Anywhere12 spatial capabilities. We have 2 tables one called "grids" and the other called "lines". The grids table is simple square polygons (i.e. 4 points) and the lines table is made up of linestrings. I would like to issue a query that selects all the linestrings contained within a specified grid. This is easily done using the ST_Intersects function but the results are not clipped. In other words the results may contain a linestring from the lines table that originates (or terminates) inside the specified grid but ends up outside it. I would like to issue a query that basically truncates the a linestring at the point of intersection with the polygon. Has anyone tries to do something like this or is it better to do this in code after you retrieve the linestring. Thanks

[VolkerBarth]

all the linestrings contained within a specified grid

I'd think that a method like ST_CoveredBy() does fit your requirements better.

[Former Member]

While Volker is correct if you truly meant "contained within" a specified grid, from the rest of your description I gather that what you are really trying to construct is for each grid cell you want the "clipping" of any linestring that intersects that cell. Here's how you might do that.

-- sample grid schema
create table grids( gridnum integer not null default autoincrement, geom ST_Polygon(SRID=0));
insert into grids(geom) values( new ST_Polygon('Polygon((-1 0, 0 0, 0 1, -1 1, -1 0))') );
insert into grids(geom) values( new ST_Polygon('Polygon((0 0, 1 0, 1 1, 0 1, 0 0))') );
-- sample line schema
create table lines( linenum integer not null default autoincrement, geom ST_LineString(SRID=0));
insert into lines(geom) values( new ST_LineString('LineString(-2 0.5, 2 0.5)') );
insert into lines(geom) values( new ST_LineString('LineString(-1 -1, 1 1)') );
-- query returning intersecting (grid,line) pairs along with their intersection
select gridnum, linenum, grids.geom.ST_Intersection( lines.geom ) as clipping
from grids, lines
where grids.geom.ST_Intersects( lines.geom ) = 1;
-- results
gridnum,linenum,clipping
1,1,LineString (-1 .5, 0 .5)
1,2,Point (0 0)
2,1,LineString (0 .5, 1 .5)
2,2,LineString (0 0, 1 1)