Please be aware that the content in SAP SQL Anywhere Forum will be migrated to the SAP Community in June and this forum will be retired.

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

asked 16 Jun '15, 08:46

Codecranker's gravatar image

accept rate: 20%

all the linestrings contained within a specified grid

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

(16 Jun '15, 09:00) Volker Barth

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
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)
permanent link

answered 16 Jun '15, 14:34

David%20DeHaan's gravatar image

David DeHaan
accept rate: 50%

edited 16 Jun '15, 14:36

Your answer
toggle preview

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here



Answers and Comments

Markdown Basics

  • *italic* or _italic_
  • **bold** or __bold__
  • link:[text]( "title")
  • image?![alt text](/path/img.jpg "title")
  • numbered list: 1. Foo 2. Bar
  • to add a line break simply add two spaces to where you would like the new line to be.
  • basic HTML tags are also supported

Question tags:


question asked: 16 Jun '15, 08:46

question was seen: 1,772 times

last updated: 16 Jun '15, 14:36