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.

Answer 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).

Answer 2

---

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);

Answer 3

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;