September 25, 2017 at 12:57 am
Hi there,
I have a db table with over 800 lines representing pathways. Each pathway contains a population and dwelling count value based on Census data.
These pathways start at a point and intersect with another path and then follow that path, with other paths joining and following until a final destination.
Where these lines meet I want to be able to merge the segments where they intersect and get the accumulated dwelling count/population just for that section, and the next section do the same. so that I can represent this in a GIS with the line thickness being the sum of population or dwelling count for all lines between each intersection. Is this possible?
With the spatial functions I would need to create a variable for each line as you can't intersect lines in the same table, and there are over 800.
I tried running a loop and putting in 800+ temp tables but understandably it is taking too long.
Is there a way I can do this?
Thanks in advance,
Josh
September 25, 2017 at 7:08 am
Without some kind of helper table that could give you some kind of structure to the pathways, it might be a tad difficult to come up with some natural grouping, unless you want to start drawing map boundaries and using spatial functions to determine your groupings, e.g. is a given point within a given defined geometric boundary. This is the kind of thing that companies like Garmin and Google and TomTom have to do. There has to be some kind of hierarchy or structure to your paths, or you have little chance of performant code.
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
October 4, 2017 at 11:26 pm
Yeah it is tricky. I tried a loop to go through each line, and within that loop another loop to go through all lines except the line in loop 1, but nothing works.
Maybe it's not possible with SQL.
October 5, 2017 at 7:22 am
joshua.james.1979 - Wednesday, October 4, 2017 11:26 PMYeah it is tricky. I tried a loop to go through each line, and within that loop another loop to go through all lines except the line in loop 1, but nothing works.
Maybe it's not possible with SQL.
The still unanswered question is the nature of the structure. If it's a true hierarchy, then a recursive CTE might get you where you need to be, but you still need to be able to describe the exact nature of the relationship between any one line and any other, by some kind of strict rule where exceptions are sufficiently specific that you can still provide a strict rule that applies to the exceptions. Do you have that information? Can you provide it here?
As to using a recursive CTE, you have to provide an anchor element (a query) that provides the top-most levels of your hierarchy, and then the recursive element (another query) is UNION ALLed to it. The recursive query selects FROM some setup in which the name of the CTE is part of the set of JOINs used, and those JOINs define the hierarchy.
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply