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

Fri, 20 Jul 2007 08:38:12 -0700


Milo van der Linden wrote:

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.
Did you know that PostGIS has a shape loader? You may find loading shape files easier with the shp2pgsql utility. Also, PostGIS can perform transformations on geometries ... you may not even need ogr. Just a thought.

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
It sounds like the geometries in your temp table either doesn't have, or has a different SRID than your production table. Is the data in your staging table supposed to be the same projection as your production table? (ogr possibly didn't add an SRID to your imported geometries and they may be defaulting to -1). If this is the case, then you simply need to wrap your geometries with a setsrid function call, ie "SetSRID(the_geom, 32767)", assuming of course that 32767 is the SRID of your production table.
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,
setsrid(wkb_geometry, 32767) as the_geom,
1 as idproject,
6 as indsh0,
id as idshp
FROM bm_staging;
Hope this helps,
-- Kevin


--------
Kevin Neufeld
Refractions Research Inc.
[EMAIL PROTECTED]
(250) 383-3022

_______________________________________________
postgis-users mailing list
[email protected]
http://postgis.refractions.net/mailman/listinfo/postgis-users

Reply via email to