I've loaded the US road shapes into MSSQL. I've determined the query to locate the segment of road for a specific address. The segment contains a "linestring" from a series of points.

I'm trying to estimate the lat/long of the actual house number.

My approach is to compare the house number to the range of numbers (so HN 13 is 30% between a road segment for house numbers 10 through 20), then determine the point of the same length along the path (so 30% down the segment, based on the earlier example)

So far i've torn the segment down into each line, and created a running total of the distance (since the house number may not be on the first or last segment)... at some point I need to identify a point along the final line... unsure how to go about.

Every calculation I see is for calculating midpoint, but given that I don't likely want a point exactly 50% between the start/end, the calculation needs to be adjusted... just not sure how.

In the unlikely event that you know of a built-in method to do this, awesome... based on my looking, I didn't find anything, so i'm not expecting.

edit: for the bounty, here's my simple test case... quick google search found the example of traveling between CYYZ (toronto) and VHHH (hong kong) to demonstrate that the process of projecting the sphere onto a 2d plane/graph doesn't always make a direct path appear straight... such an example should cover the curvature requirements (so not doing a simple algebraic lat long percentage)

DECLARE @CYYZ geography = geography::STGeomFromText('POINT(-79.6270084 43.6777176)', 4326)
DECLARE @VHHH geography = geography::STGeomFromText('POINT(113.9162921 22.3080519)', 4326)
DECLARE @path geography = geography::STLineFromText('LINESTRING(-79.6270084 43.6777176, 113.9162921 22.3080519)', 4326);  

          SELECT 'PATH' , @path
UNION ALL SELECT 'START', @CYYZ.STBuffer(500000) -- buffer converts point to circle with
UNION ALL SELECT 'END'  , @VHHH.STBuffer(500000) -- radius, just for visibility
) A(Term, Geom)

the example is for MSSQL, since that's my platform, but the coords should work either way (note that i may have inadvertently swapped the lat/long for both points)

  • Use function postgis.net/docs/ST_LineInterpolatePoint.html – Cyril M May 25 '18 at 6:46
  • @Cyril as mentioned in post, MS SQL, not postgres. – Scott Brickey May 25 '18 at 11:32
  • So what's the problem, go to PostgreSQL, it's a joke, and if seriously, I just did not carefully read the question, I apologize – Cyril M May 25 '18 at 11:38
  • It is possible, that in MS SQL there should be a similar function – Cyril M May 25 '18 at 11:40

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.