pgAdmin III SQL creating a point

Question

I have created a table, dc_crimes, that has eight columns. I would like to fill the geom column with point data created from the lat and long column. The syntax I used to create the table is:

CREATE TABLE tmorr.dc_crimes
(
gid serial NOT NULL,
shift character varying(80),
method character varying(80),
offense character varying(80),
ward character varying(80),
latitude numeric,
longitude numeric,
geom geometry(Point,102285)

I then inserted data from a previous table. Syntax used:

INSERT INTO tmorr.dc_crimes (gid,shift,method,ward,latitude,longitude)
SELECT gid,shift,method,ward,latitude,longitude FROM tmorr.dc_crimes_v

This is where I am a bit lost. I tried to update the dc_crimes table and use the ST_MakePoint syntax to create points. Syntax used:

UPDATE tmorr.dc_crimes SET geom = ST_MakePoint(longitude, latitude);

This returned an error:Geometry SRID (0) does not match column SRID (102285) SQL state: 22023

So I tried to set the geometry SRID using this syntax:

SELECT UpdateGeometrySRID('dc_crimes','geom', 102285);

This also gave me an error: ERROR: column not found in geometry_columns table CONTEXT: PL/pgSQL function updategeometrysrid(character varying,character varying,character varying,character varying,integer) line 36 at RAISE SQL statement "SELECT UpdateGeometrySRID('','',$1,$2,$3)" PL/pgSQL function updategeometrysrid(character varying,character varying,integer) line 5 at SQL statement

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

ERROR: column not found in geometry_columns table SQL state: P0001 Context: PL/pgSQL function updategeometrysrid(character varying,character varying,character varying,character varying,integer) line 36 at RAISE SQL statement "SELECT UpdateGeometrySRID('','',$1,$2,$3)" PL/pgSQL function updategeometrysrid(character varying,character varying,integer) line 5 at SQL statement

I believe I need to put values in the ST_MakePoint() but I am not sure which points.

I'm surprised that PostGIS is not mentioned in your question. — PolyGeo
– PolyGeo ♦, Commented Jul 25, 2017 at 22:10

AJSmyth · Accepted Answer · 2017-07-25 22:17:17Z

1

UpdateGeometrySRID() is unnecessary because your column already has an SRID set, as indicated in your error message. You need to do something like this:

UPDATE tmorr.dc_crimes 
SET geom = ST_SetSRID(ST_MakePoint(longitude, latitude), 102285);

(Assuming that 102285 is the actual SRID of your data).

answered Jul 25, 2017 at 22:17

AJSmyth

4234 silver badges10 bronze badges

combining your feedback with the below feedback solved the issue.

Tiffany Morris
– Tiffany Morris

2017-07-26 01:09:35 +00:00
Commented Jul 26, 2017 at 1:09

Add a comment |

Muhammad Kamran · Accepted Answer · 2017-07-26 00:13:52Z

I usually tackle this task as follow.

First create table without Geometry column

CREATE TABLE tmorr.dc_crimes
(
gid serial NOT NULL,
shift character varying(80),
method character varying(80),
offense character varying(80),
ward character varying(80),
latitude numeric,
longitude numeric )

Then I populate table with data

INSERT INTO tmorr.dc_crimes (gid,shift,method,ward,latitude,longitude)
SELECT gid,shift,method,ward,latitude,longitude FROM tmorr.dc_crimes_v

After that, I create a geom column by simply using

Alter table tmrr.dc_crimes Add column geom geometry;

Now, as Ajsymth has answered, you can run command like this.

UPDATE tmorr.dc_crimes 
SET geom = ST_SetSRID(ST_MakePoint(longitude, latitude), 102285);

combining your feedback with the Ajsymth's feedback solved the issue. — Tiffany Morris
– Tiffany Morris, Commented Jul 26, 2017 at 1:10

Kiruahxh · Accepted Answer · 2021-12-10 15:19:43Z

0

UpdateGeometrySRID triggers an exception when postgis is not in the search_path. You can try this:

set search_path to your_schema, postgis_shema; -- replace with the correct schema names
SELECT UpdateGeometrySRID('dc_crimes','geom', 102285);

In my case I installed postgis in public schema, but it could have been installed in postgis schema, so I do:

set search_path to my_schema, postgis, public;

answered Dec 10, 2021 at 15:19

Kiruahxh

1133 bronze badges

Add a comment |

Stack Exchange Network

pgAdmin III SQL creating a point

3 Answers 3

Hot Network Questions

pgAdmin III SQL creating a point

3 Answers 3

Related

Hot Network Questions