[postgis-users] Copy geometry from one table to another with insert statement fails

[Milo van der Linden]

Hello list,

I am relatively new to PostGIS but I love it already, I am a GIS
specialist with 10 years experience on closed source and 5 on Open
Source. Currently I am busy to import operation data for use with a
webbased GIS.

The original data is in shape files. With ogr2ogr I do a conversion from
the Dutch coordinate system to WGS84 and import it in a temp_table in
PostGIS.

The next step I want to take is insert the records from the temp_table
into the production table. For this I am setting up an INSERT statement
in SQL

My statement is as follows:

INSERT INTO public.infra_line2
(infraline_type,
infraline_polution,
infraline_comment,
infraline_startdate,
infraline_enddate,
infraline_top,
infraline_base,
the_geom,
idproject,
idsh0,
idshp)
SELECT
'type' as infraline_type,
CASE WHEN ver_inr='0' THEN FALSE WHEN ver_inr='1' THEN TRUE ELSE FALSE
END as infraline_polution,
opmerking as infraline_comment,
d_start as infraline_startdate,
d_stop as infraline_stopdate,
hoogte_top as infraline_top,
hoogte_bas as infraline_base,
wkb_geometry as the_geom,
1 as idproject,
6 as indsh0,
id as idshp
from bm_staging;

When I execute the statement in pgAdmin, I get an error message from
postGIS:

ERROR:  new row for relation "infra_line2" violates check constraint
"enforce_srid_the_geom"

I tried to add USING SRID=32767 to the insert statement, but it fails
with another error message

DELETE FROM public.infra_line2;

INSERT INTO public.infra_line2
(infraline_type,
infraline_polution,
infraline_comment,
infraline_startdate,
infraline_enddate,
infraline_top,
infraline_base,
the_geom,
idproject,
idsh0,
idshp)
SELECT
'type' as infraline_type,
CASE WHEN ver_inr='0' THEN FALSE WHEN ver_inr='1' THEN TRUE ELSE FALSE
END as infraline_polution,
opmerking as infraline_comment,
d_start as infraline_startdate,
d_stop as infraline_stopdate,
hoogte_top as infraline_top,
hoogte_bas as infraline_base,
wkb_geometry as the_geom,
1 as idproject,
6 as indsh0,
id as idshp
from bm_staging USING SRID=32767;

When I execute the statement from pgAdmin, it returns:

ERROR:  syntax error at or near "USING" at character 638

What am I doing wrong?

Any help highly appreciated!

Kind regards,

-- 


	

Milo van der Linden
mlinden at zeelandnet.nl <mailto:mlinden at zeelandnet.nl>
milovanderlinden at gmail.com <mailto:milovanderlinden at gmail.com>
milo at 3dsite.nl <mailto:milo at 3dsite.nl>
http://www.3dsite.nl

	  	

De informatie in dit bericht reflecteerd mijn persoonlijke mening en
niet die van een bedrijf of instantie. Aan de informatie kunnen geen
rechten worden ontleend. Indien dit bericht onderdeel is van een forum,
mailing-list of community dan gelden automatisch de bijbehorende
voorwaarden.

-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/postgis-users/attachments/20070720/8903438d/attachment.html>