2

I have a Postgresql table with a text column that has data like the below.

{"type":"MultiLineString","coordinates":[[[-122.995882414,44.05766941700006],[-122.99818210099994,44.05768878400005]]]}

I have added a new column to the table with type Geography and I would like to move this multilinestring data to the new geography column.

I need to find the centroid of the mulitilinestring in the form of lat/lng so that distance can be calculated from a give address. The table has around 139k rows.

3

You can use ST_FromGeoJson to convert the text to a geometry, then you would cast the result to the desired geography format. At last, you can use ST_Centroid

ex:

select st_AsText(st_geomFromGeoJson('{"type":"MultiLineString","coordinates":[[[-122.995882414,44.05766941700006],[-122.99818210099994,44.05768878400005]]]}'));
                                     st_astext
------------------------------------------------------------------------------------
 MULTILINESTRING((-122.995882414 44.0576694170001,-122.998182101 44.0576887840001))

with the cast:

select st_geomFromGeoJson('{"type":"MultiLineString","coordinates":[[[-122.995882414,44.05766941700006],[-122.99818210099994,44.05768878400005]]]}')::geography;

... and the centroid

select st_centroid(st_geomFromGeoJson('{"type":"MultiLineString","coordinates":[[[-122.995882414,44.05766941700006],[-122.99818210099994,44.05768878400005]]]}')::geography);

From a column:

SELECT st_centroid(st_geomFromGeoJson(my_geoJson_column)::geography)
FROM myTable;
  • 1
    The only further item to note is that the geography created by this ST_GeomFromGeoJSON example has an SRID of zero. A geography with a SRID of zero is not very useful so the example should probably use ST_SetSRID to add SRID 4326 to the intermediate geometry conversion before casting it to geography. – pauldzy Jul 7 at 13:29

Your Answer

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

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