CODEWITHSUNDEEP
phppostgresqlgoogle-maps-api-3yii2postgis
Maurocrispin
Maurocrispin

Reputation: 242

Geometry polygon to lat long on postgis

I need to convert potsgres geometry data type to lat long.

This is my table enter image description here

geom column stores coordinates but they are not human readable.

I just cant figure out how I can get every single point that makes a polygon.

There are some output options for geometry data type https://postgis.net/docs/reference.html#Geometry_Outputs.

I need every point lat long because I have to use them in this code to define and render a polygon on google maps 

$coords = [
          new LatLng(['lat' => 25.774252, 'lng' => -80.190262]),
          new LatLng(['lat' => 18.466465, 'lng' => -66.118292]),
          new LatLng(['lat' => 32.321384, 'lng' => -64.75737]),
        ];

        $polygon = new Polygon([
          'paths' => $coords
        ]);

The problem is that I cant access lat long to define points that meakes up a polygon.

I'm using YII2, yii2-google-maps extension, PostGis Postgress.

Upvotes: 1

Views: 8317

Answers (1)

Jim Jones
Jim Jones

Reputation: 19653

For displaying geometries in WKT use the function ST_AsText:

SELECT ST_AsText(geom) FROM colonias

In case you prefer GeoJSON or GML, use the following functions:

SELECT ST_AsGeoJSON(geom) FROM colonias;
SELECT ST_AsGML(geom) FROM colonias

See also this other answer for more output formats in PostGIS.

To split longitude and latitude use the functions ST_X and ST_Y:

SELECT ST_X(geom),ST_Y(geom) FROM colonias

In case your geometries are not points, you have to first extract the points using ST_DumpPoints an then parse them as described above. A straightforward way to do so is using CTE (aka WITH clause).

Example:

WITH j AS (
 SELECT 
  (ST_DumpPoints(
    ST_GeomFromText('POLYGON((2 0,2 3,0 2,2 0))'))).geom AS point
) 
SELECT ST_X(j.point), ST_Y(j.point) FROM j;

Demo: db<>fiddle

Upvotes: 8

Related Questions