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

[Milo van der Linden]

Hello Kevin, thank you for the response!

Kevin Neufeld schreef:
>
> 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.
I knew that, but I am very familiar with ogr and not with shp2pgsql.
Besides that, it is fired from a php script that is currently well under
control. Also, shp2pgsql is from the same development team as ogr, they
probably use the same code base.
>>
>> 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.
When I check my geometry meta table, the SRID for the staging table is
correct, even though it should be 4326 (wgs84), during the import ogr
decided to create a new srid that is a perfect copy of the standard
wgs84 SRID.
>
>     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,
Thank you for giving it a try, but no, it doesn't. I need to know why
the error is generated. Perhaps some one from postGIS development can
tell me?
> -- Kevin
>
>
> --------
> Kevin Neufeld
> Refractions Research Inc.
> kneufeld at refractions.net
> (250) 383-3022
>


-- 


	

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.


-- 


	

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/02c0badf/attachment.html>