1

I hope you guys can help me or at least point me in the right direction. I have a SQL Server table that represent the boundaries of a Polygon. It roughly looks like this in a simplified version:

=====================================================
Route id     |   pointId    |      Lat      |   Lon
=====================================================
     1       |      1       |  19.12873     | -100.01221
     1       |      2       |  19.65468     | -100.01221
     1       |      3       |  19.65465     | -100.01221
     1       |      1       |  19.65468     | -100.01221
     1       |      2       |  19.35468     | -100.01221
     1       |      3       |  19.68798     | -100.01221
     1       |      4       |  19.98785     | -100.01221
     2       |      1       |  19.12873     | -100.01221
     2       |      2       |  19.12873     | -100.01221
     2       |      3       |  19.12873     | -100.01221

As you can see, there are routes that have two polygons in it, the way to know it is by looking at the consecutive numbers of the pointId field. If the id resets to one but the route is the same then what you have is a route with two Polygons in it. The final goal of my SQL script is to process every route and every Polygon to determine if a point is inside any of them.

What I have done is the following:

  1. Have a cursor that brings all the distinct route id
  2. Process each line to filter and get the coordinates of each route

Right now I need to separate each query into separate trunks following the consecutive numeration. How can I do that to avoid using another cursor and evaluating each line to detect the change in numeration? Is there a type of query or instruction to accomplish this?

Once separated, I can then parse each Polygon into a geometry object and check whether a point is in the boundaries of the polygon or not.

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
l_degaray
  • 63
  • 9

1 Answers1

0

You could do something like this, if I follow your question correctly. It makes an incrementing row_number over your conscutive values. As long as they increment by one, and the row_number does too, the next row will appear in that "group"

select 
    Grp = PointId - row_number() over (partition by route_id order by pointid),
    route_id,
    pointid,
    lat,
    lon
from yourTable
Xedni
  • 3,662
  • 2
  • 16
  • 27
  • If I order de point ids I would automatically loose the polygon sequence and I wouldn't distinguish which order should I follow. The order would be point 1 of first polygon, point 1 of second polygon and so on.. – l_degaray Mar 20 '18 at 23:10
  • Can you indicate what you expect your results to look like then? – Xedni Mar 20 '18 at 23:11