0

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 Jul 25 '17 at 22:10
0

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 Jul 26 '17 at 1:10
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).

  • combining your feedback with the below feedback solved the issue. – Tiffany Morris Jul 26 '17 at 1:09

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.