srid.sql

53 views
Skip to first unread message

swin...@gmail.com

unread,
Aug 28, 2008, 2:53:08 PM8/28/08
to zigGIS
Hi, I am new to zigGis, PostGIS. So I'm a little lost, but finding my
way. Can someone explain how to apply the srid.sql to the POSTGIS
server? Thanks,

Marc

Abe Gillespie

unread,
Aug 28, 2008, 3:31:49 PM8/28/08
to zig...@googlegroups.com
There are two ways.

pgAdmin:
Open up pgAdmin, select the PostGIS database and click the SQL button.
From the SQL Query window, open up srid.sql. Click the "play"
button. You'll see a bunch of warnings but it will execute
successfully.

Command Line / psql:
Note that you'll either have to have your PATH environment variable
set or do this from within the Postgres bin directory. Enter the
command:
psql -u username -d the_posgis_database < srid.sql

-Abe

Paul

unread,
Sep 1, 2008, 7:52:01 PM9/1/08
to zigGIS
Tried three options for adding the ESRI entries to the
spatial_ref_sys; pgAdmin query window, psql command line with of
option and psql command line with input redirect using "<". All three
attempts return this series of errors, although the command line
redirect did seem to work (the first two did not):

WARNING: nonstandard use of escape in a string literal
LINE 4: VALUES (4023, 'ESRI', 4023,
'GEOGCS[\"GCS_International_1967...
^
HINT: Use the escape string syntax for escapes, e.g., E'\r
\n'.WARNING: nonstandard use of escape in a string literal
LINE 7: VALUES (4217, 'ESRI', 4217,
'GEOGCS[\"GCS_Bern_1898\",DATUM[...
^
HINT: Use the escape string syntax for escapes, e.g., E'\r
\n'.WARNING: nonstandard use of escape in a string literal
LINE 10: VALUES (4404, 'ESRI', 4404,
'GEOGCS[\"GCS_Montserrat_1958\",...
^
HINT: Use the escape string syntax for escapes, e.g., E'\r
\n'.WARNING: nonstandard use of escape in a string literal
LINE 13: VALUES (20002, 'ESRI', 20002,
'PROJCS[\"Pulkovo_1995_GK_Zone...

...
...

This series of errors continued for each line in the srid.sql file...

-Paul


On Aug 28, 2:31 pm, "Abe Gillespie" <abe.gilles...@gmail.com> wrote:
> There are two ways.
>
> pgAdmin:
> Open up pgAdmin, select the PostGIS database and click the SQL button.
>  From the SQL Query window, open up srid.sql.  Click the "play"
> button.  You'll see a bunch of warnings but it will execute
> successfully.
>
> Command Line / psql:
> Note that you'll either have to have your PATH environment variable
> set or do this from within the Postgres bin directory.  Enter the
> command:
> psql -u username -d the_posgis_database < srid.sql
>
> -Abe
>
> On Thu, Aug 28, 2008 at 2:53 PM, swingt...@gmail.com

Abe Gillespie

unread,
Sep 1, 2008, 11:15:50 PM9/1/08
to zig...@googlegroups.com
These are warnings and you probably didn't have any errors. To double
check, run this query:

select * from spatial_ref_sys where auth_name='ESRI';

If it returns any records then you're golden.

Thanks.
-Abe

Art-o

unread,
Oct 23, 2010, 8:26:16 AM10/23/10
to zig...@googlegroups.com
I tried the first (pgAdmin) method on a PC, but the coordinate systems
definitions did not go in the table.
Starting:
-- Executing query:
BEGIN;

INSERT INTO "spatial_ref_sys" ("srid", "auth_name", "auth_srid",
"srtext", "proj4text")


VALUES (4023, 'ESRI', 4023,

'GEOGCS[\"GCS_International_1967\",DATUM[\"D_International_1967\",SPHEROID[\"International_1967\",
6378160,298.25]],PRIMEM[\"Greenwich\",0],UNIT[\"Degree\",
0.017453292519943295]]', '+proj=longlat +ellps=aust_SA');

INSERT INTO "spatial_ref_sys" ("srid", "auth_name", "auth_srid",
"srtext", "proj4text")


VALUES (4217, 'ESRI', 4217,

'GEOGCS[\"GCS_Bern_1898\",DATUM[\"D_Bern_1898\",SPHEROID[\"Bessel_1841\",
6377397.155,299.1528128]],PRIMEM[\"Greenwich\",0],UNIT[\"Degree\",
0.017453292519943295]]', '+proj=longlat +ellps=bessel');

... and ending:

HINT: Use the escape string syntax for escapes, e.g., E'\r\n'.

WARNING: nonstandard use of escape in a string literal

LINE 1401: VALUES (104261, 'ESRI', 104261,
'GEOGCS[\"GCS_Merchich_Degre...


^
HINT: Use the escape string syntax for escapes, e.g., E'\r\n'.

WARNING: nonstandard use of escape in a string literal

LINE 1404: VALUES (104304, 'ESRI', 104304,
'GEOGCS[\"GCS_Voirol_1875_De...


^
HINT: Use the escape string syntax for escapes, e.g., E'\r\n'.

WARNING: nonstandard use of escape in a string literal

LINE 1407: VALUES (104305, 'ESRI', 104305,
'GEOGCS[\"GCS_Voirol_Unifie_...


^
HINT: Use the escape string syntax for escapes, e.g., E'\r\n'.

ERROR: duplicate key value violates unique constraint
"spatial_ref_sys_pkey"

********** Error **********

ERROR: duplicate key value violates unique constraint
"spatial_ref_sys_pkey"
SQL state: 23505

-- Executing query:


select * from spatial_ref_sys where auth_name='ESRI';

Total query runtime: 110 ms.
0 rows retrieved.

Should try the command line method?

Arto

Abe Gillespie

unread,
Oct 25, 2010, 8:44:04 AM10/25/10
to zig...@googlegroups.com
Newer versions of PostGIS have one of the SRIDs already installed and
therefore it causes the duplicate key violation. When I have more
time I'll try to figure out which one is causing the dupe and / or
come up with a better method to handle this. For now do this:

Remove the BEGIN; and END; lines from the top and bottom of the
script. Then run it. Remove, one-by-one, each INSERT INTO [...]
statement from the top and run. Do this until it succeeds.

IIRC it's one of the first 10 statements that's causing the problem.
So it shouldn't be too much work. Please report back here if you
figure out which one is causing the problem.

Thanks.
-Abe

> --
> You received this message because you are subscribed to the Google Groups "zigGIS" group.
> To post to this group, send email to zig...@googlegroups.com.
> To unsubscribe from this group, send email to ziggis+un...@googlegroups.com.
> For more options, visit this group at http://groups.google.com/group/ziggis?hl=en.
>
>

Reply all
Reply to author
Forward
0 new messages