ogr2ogr add geometry from x y when importing to postgis

Question

Im importing a xlsx with x y coordinates to a postgis database, with the following command:

ogr2ogr -overwrite -progress -nln data -nlt POINT -f PostgreSQL "PG:host=Localhost user=postgres password=Postgres dbname=database" -nln data C:\data\data.XLSX 

It successfully add the data to my database. With some sql included in the command i will now like to take the x y columns and update the (empty) geometry column, so that the x y data is converted to geometry.

I tried to add:

-sql "SELECT AddGeometryColumn ('database', 'data', 'geom', 26913, 'POINT', 2)" -dialect sqlite

Just to try to add / define some geometry to my table.

Any suggestion on where to look or what to do?

Answer 1

If the data is now in your database you can use ST_MakePoint or ST_GeomFromText to construct a geometry object from your x and y columns, e.g.:

--ST_SetSRID is also used here as ST_MakePoint will construct a point with an unknown SRID
select ST_SetSRID(ST_MakePoint(x,y),26913) from data

or:

--Here, you have to concatenate your x and y values to form a WKT string as 
--ST_GeomFromText requires text input
select ST_GeomFromText('POINT(' || x || ' ' || y || ')',26913) from data

Whichever method you use, you can incorporate it into an UPDATE statement to update your geom column values with the returned geometries:

UPDATE data
SET geom = ST_SetSRID(ST_MakePoint(x,y),26913)

If you want to have the geometries created as part of the ogr2ogr import process you just need to include an sql statement that creates the geometries (as above). I've adapted your ogr2ogr statement to include an sql statement which will return all the fields from your xlsx file and a new geom column constructed using ST_GeomFromText:

ogr2ogr -overwrite -progress -nln data -nlt POINT -sql "select *, ST_GeomFromText('POINT(' || x || ' ' || y ||')',26913) geom from Sheet1" -dialect sqlite -f PostgreSQL "PG:host=localhost user=postgres password=Postgres dbname=database"  C:\data\data.xlsx

*I've assumed your data is in a worksheet called Sheet1 within your xlsx file