Convert Geometry from T-SQL to Postgres

Question

I have this column for creating a geometry from SQL Server:

Shape  AS ([geometry]::STGeomFromText(((('POINT('+CONVERT([varchar](20);,Longitude))+' ')+CONVERT([varchar](20),Latitude))+')',(4326))), PRIMARY KEY CLUSTERED

I need to create the column from SQL Server to Postgres:

CREATE EXTENSION postgis;

CREATE EXTENSION postgis_topology;

I also changed the STGeomFromText to ST_GeomFromText but I'm getting an error in pgAdmin:

ERROR:  syntax error at or near "AS"
LINE 32:  Shape  AS ([geometry]::STGeomFromText(((('POINT('+CONVERT([...
             ^

********** Error **********

ERROR: syntax error at or near "AS"
SQL state: 42601
Character: 818

Did I missed something to install for postgis or the syntax needs a complete overhaul for postgres?

Unrelated, but: Postgres 9.1 is no longer supported you should plan an upgrade to the current version as soon as possible. — user330315
– user330315, Commented Jul 31, 2019 at 9:12

score 2 · Accepted Answer · 2019-08-01 10:46:57Z

2

That syntax is completely invalid for Postgres (and not valid standard SQL either). It seems you blindly copied that definition from SQL Server

The string concatenation operator is || in SQL, not +.
Square brackets are invalid for identifiers in SQL.
Postgres has no CLUSTERED indexes.

But most importantly: Postgres currently (Postgres 11) has no computed (generated) columns so you can't define a column as a computed on using Shape AS <expression>

Collectives™ on Stack Overflow

Convert Geometry from T-SQL to Postgres

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related