[GIS] Offseting point perpendicular to line direction in PostGIS

linestringoffsetperpendicularpointpostgis

I have a PostGIS table of points, that are sitting on road centerlines (I've linear referenced them). And the second table with road centerlines themselves.

The point table also has a field roadid and a field "offst", that represents the offset in meters (negative values = offest to the left, positive = offset to the right side of the road).

The question is how can I calculate a point geometry, that is offsetted perpendicular to road direction, according to the value of the "offst" field?

Best Answer

There is probably easier way but... in PostGIS 2.0.1 ->

https://postgis.net/docs/ST_OffsetCurve.html

take road, offset it needed amount using ST_OffsetCurve and then use ST_Line_Locate_Point to offsetted line and then ST_Line_Interpolate_Point to find correct place

**SELECT ST_Line_Interpolate_Point(ST_Line_Locate_Point(ST_OffsetCurve(roadcenter_geom, pointoffset), point_geometry ) from xx where yyy**

Another way is get vertex where point intersects line and calculate azimuth there and then use trigometry to figure where new point should be

Edit : Also i found this one :

http://trac.osgeo.org/postgis/wiki/UsersWikiExamplesInterpolateWithOffset

"This code creates another st_line_interpolate_point with a 3rd argument for offset of the point to the left or right side of the line segment. Useful for Geocoding."

Which is ready made implementation of "Another way"

Related Solutions

[GIS] Adding offset to GeoJSON layer in Leaflet

According to Romain D on it1me.com, it can be done with the Leaflet.PolylineOffset as referenced in the comments by MattPil29 above.

I have adapted it for the data in your example. I turned off your original line by changing opacity to 0 in myStyle. There is probably a more elegant way to not add it.

The other key is flipping the x,y coordinate to make L.Polyline happy.

See a fiddle here, Leaflet Offset geojson

Main code

var myLines = [{
  "type": "LineString",
  "coordinates": [[-100, 40], [-105, 45], [-110, 55]]
}, {
  "type": "LineString",
  "coordinates": [[-110, 55], [-105, 45], [-100, 40]]
}];

var myStyle = {
  "color": "#ff7800",
  "weight": 5,
  "opacity": 0,
  "offset": 1.5
};

L.geoJson(myLines, {
  style: myStyle,
  onEachFeature: function(feature){
    var coords = feature.coordinates;
    var lengthOfCoords = feature.coordinates.length;

    //reverse x, y order for L.polyline
    for (i=0;i<lengthOfCoords;i++)
      {
      // swap x and y, save x in var holdLon then drop back into second position
        holdLon = coords[i][0];
        coords[i][0] = coords[i][1];
        coords[i][1] = holdLon;
     }

    var offset = L.polyline(coords, {offset: 5, color: 'black'}).addTo(map);
  }
}).addTo(map);

edit: If you don't want the original lines at all just remove the

.addTo(map)

in the last line, leave it as

)};

Then you can remove

style: myStyle,

[GIS] Order by geometry columns

I'm not sure a natural ordering really exists for a geometry, given that it is two-dimensional and can start from any point in its outer linear ring. However, to answer the second part of your question, the following will order by the left-most x coordinate of every geometry.

Select * from island_table order by ST_XMin(ST_Envelope(geom));

EDIT: As pointed out by MikeT, the call to ST_Envelope is superfluous, as the bounding box is available anyway to any indexed geometry, so it is sufficient just to write:

Select * from island_table order by ST_XMin(geom);

ST_Envelope gives you the bounding box, from where you can access the minimum and maximum values of x and y, using ST_XMin, ST_YMin, ST_XMax, etc.

I just did a quick test on some test polygons and if you sort by the WKT representation of a geometry,

Select * from island_table order by ST_AsText(geom);

you get something like,

POLYGON((100.00,.....

POLYGON((101.86,.....

POLYGON((102.17,.....

POLYGON((11.82,.....

that is in text search order.

Whereas if you search by the geometry itself, that is,

Select st_astext(geom) from island_table order by geom;

it sorts by actual values of the first x coordinate in the geometry, eg,