[postgis-users] Regularly spaced address markers on alternating side of road

[Dan Jacobson]

Dear PostGis list, I want to make pseudo-addresses every 25 meters along
a road,

21  23  25  27  29
==================
  22  24  26  28

Sort of like I did a long time ago in the images on
http://jidanni.org/geo/house_numbering/mountain.html
with a different GIS. But at that time I only put them on the centerline.

OK I found
http://postgis.refractions.net/documentation/manual-svn/reference.html#Linear_Referencing

So I suppose I should: (0. apt-get install postgis etc. here on Debian.)
1. Learn how to import my vector roads from .kmz into postgis. (I'll Google that.)

2. Use http://postgis.net/docs/ST_Length.html to get the length (L in meters) of each
of my roads.

3. Write the equivalent of perl:
for($a_fraction=0; $i <= 1; $house_number++, $a_fraction += 25/$L){print $house_number at
location (ST_Line_Interpolate_Point(..., $a_fraction));}

4. For extra bonus, figure out to offset the label:
perpendicular to the road at 3 * ($address % 2 ? -1 : 1) meters.

Thanks.

>>>>> "RH" == Régis Haubourg <regis.haubourg at gmail.com> writes:

RH> Hi Dan, 

RH> Usually, I solve this with postgis or sqlite view, using Linear
RH> Referencing functions. I just create a view or a query of
RH> spatialised vertices. Some SQL magic could also calculate rotation
RH> and offset in additional fields to align labels with the lines. 
RH> Cheers Régis