Take the 2-minute tour ×
Geographic Information Systems Stack Exchange is a question and answer site for cartographers, geographers and GIS professionals. It's 100% free, no registration required.

I'm trying to create a linestring out of geography points. At the moment I'm doing it like this:

select ST_GeographyFromText(ST_Makeline(a.record_point)) from (
select (record_point::geometry)
from records order by record_timestamp) a  

Problem is that most points have unnecessary decimal places (6 is sufficient) and some points are exactly the same. I can set the decimal place precision when converting to KML but is there another way to do this and how to get rid of overlapping points?

My own solution: It is PostgreSQL specific because of the window function. Basically I just leave out spatially equal sequential geometry points and reduce the precision with geojson converter (kml converter has the same option).

select st_asgeojson(ST_MakeLine(points.record_point),6) from (
select (record_point)::geometry as record_point
lag( (record_point)::geometry, 1) over w as prev_point,
from records 
window w as (order by record_id)
order by record_timestamp
) points where not st_equals(record_point, prev_point)
share|improve this question
add comment

3 Answers 3

PostGIS has a "cleangeometry()" function which I believe works like SpatiaLite's "SanitizeGeometry()".

My mantra is that there is never a single solution in GIS, so here's two other off-beat approachs:

  1. If you have multiple lines you could pack them all in a multilinestring and use the ST_LineMerge function. It will kill the duplicates and join the lines efficiently. You could then explode the multilinestring
  2. You could simplify the geometery with a small tolerance. Simple and effective :)
share|improve this answer
    
cleangeometry() link didnt work for me, its output was multilinestring, which made result even bigger in size. I think its because it uses st_union function internally. I posted my solution under my question. –  Tamm Mar 22 '12 at 11:31
add comment

Please do not forget that you are still inside a RELATIONAL with geography database.

One way to eliminate duplicated rows( in this case points ) is to append the group by operator. You can also use the postgresql good distinct on clause.

To remove point precision you can snap vertices to desired precision. I put a chaned query below. Fell free to fix any mistakes I could have maded during the typing.

-- assume a projection with meter units --
SELECT ST_GeographyFromText( ST_Makeline( ST_snapToGrid( a.record_point, 0.01 ) ) )
FROM
(
  SELECT (record_point::geometry)
  FROM records
  GROUP BY (record_point::geometry)
  ORDER BY record_timestamp
) a

I also confirm that the cleanGeometry routine did not worked on several cases reported by clients. His use is not necessary with the new postgis 2.

share|improve this answer
add comment

If an 'efficient geometry' means removing nodes on a polyline that are unnecessary to define the shape, use PostGIS's ST_Simplify . It uses the Douglas-Peucker algorithm to thin nodes. You've got some control over how 'simplified' you get.

SELECT ST_Simplify( ST_GeographyFromText(ST_Makeline(a.record_point)), 100 ) as the_geom FROM...

And lastly, since you're storing your result as a record with a geometry data type, you don't care how many decimal points you carry. You can reduce the precision of your exported geometry later, if you wish.

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.