Re: [postgis-users] Transform from NAD27 (SRID 4267) to WGS84 (SRID 4326)
On Thu, Apr 05, 2012 at 09:25:40AM +1000, Evan Martin wrote: 70 west, 10 north is in venezuela which is outside the bounds of available NAD27 to NAD83 conversion files. Try (-120 40). Thanks, Frank. Yes, (-120 40) works. Just to confirm, will PostGIS always return the input coordinates now when they're outside the bounds of the input datum, rather than throwing an error? Is that the expected behaviour? PostGIS-1.5.x will throw an exception. PostGIS-2.0.0 will throw an exception. --strk; ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
Re: [postgis-users] Importing timezone shape files - newbie
Hi all, I'm using PG 9.1.3 with pgAdmin 1.14.2 on Windows 7 Home Edition to get familiar with PostGIS and TimeZone data. PostGIS 1.5.3 is installed and the postgis database was created by the installer. My immediate end goal is to be able to take a Lat/Lon and query PG to find the correct time zone for the location. I installed the PostGIS Shapefile and DBF loader plugin in pgAdmin but it is greyed out in the Plugins menu. I found this site with the shape files: http://efele.net/maps/tz/world/ It has two shape files: The tz_world shapefile captures the boundaries of the TZ timezones across the world, as of TZ 2011b. The geometries are all POLYGONs, and a TZ timezone will sometimes have multiple polygons. There are about 28,000 rows. The tz_world_mp shapefile captures the same boundaries. The geometries are either POLYGONs or MULTIPOLYGONs, and there is a single geometry for each TZ timezone. There are 394 rows. First question is, Which shape file to use. I don't understand the difference between the two and the merits of each. Secondly, clues on how to import this data correctly. Any other pointers are welcome! I'm not sure yet, how to do the SQL to make the query. Thanks! Scott Hi Scott, Can't tell which file is better. It really depends on what you want to do. Concerning loading file to postgis, you can use the command line shp2pgsql program: shp2pgsql -IiDS -s 4326 -g geom tz_world.shp timezone | psql gis20 (running shp2pgsql without arguments will give you the list of available switches). Nicolas ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
[postgis-users] about 64-bit postgis
hi where can i find postgis 1.5 for 9.1 postgresql 64-bit(windows) installer? best regards superman0920___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
Re: [postgis-users] about 64-bit postgis
Hello, On the website e.g http://www.postgis.org/download/windows/#windbinaries Regards ThomasG ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
Re: [postgis-users] ERROR: Geometry has Z dimension but column does not
Hi Jack, I believe that your problem is that the 4326 projection does have a 3rd dimension. I think I saw that 4329 is the one you'll want. cheers, a On Thu, Apr 5, 2012 at 9:34 AM, Gold, Jack L (US SSA) jack.g...@baesystems.com wrote: Recently updated to beta5 and now I’m having problems with selections on a geography column when I do ST_geographyFromText(’0 0 0’, 4326). I get: ERROR: Geometry has Z dimension but column does not. The column is defined as a geography (PointZ, 4326). Is this now incorrect? ** ** --Jack Gold ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users -- Andrew W. Hill www.vizzuality.com ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
Re: [postgis-users] ERROR: Geometry has Z dimension but column does not
On Thu, Apr 05, 2012 at 09:34:32AM -0400, Gold, Jack L (US SSA) wrote:
Recently updated to beta5 and now I'm having problems with selections on a
geography column when I do ST_geographyFromText('0 0 0', 4326). I get:
ERROR: Geometry has Z dimension but column does not. The column is defined
as a geography (PointZ, 4326). Is this now incorrect?
Jack,
'0 0 0' is not a valid geography representation,
and there's no ST_GeographyFromText taking a SRID
argument.
Please provide the exact query you're using, and
exact error message.
--strk;
,--o-.
| __/ |Delivering high quality PostGIS 2.0 !
| / 2.0 |http://strk.keybit.net - http://vizzuality.com
`-o--'
___
postgis-users mailing list
postgis-users@postgis.refractions.net
http://postgis.refractions.net/mailman/listinfo/postgis-users
Re: [postgis-users] navigation tracks (x,zy) h
Thanks Steve, let me explain you a bit the problem: 1. the geographical positions of these tracks were recorded with a device without enough precision, this is, instead of having for example: 67.534221 9.988212 67.534223 9.988210 67.534225 9.988207 I'm getting: 67.534100 9.987500 67.534100 9.987500 67.534100 9.987500 So I need to smooth such thing. 2. I tried st_simplify, but the problem is that this method doesn't produce curves, I tried many values but at the end I just could get a straight line, and that doesn't reflect the curves of these tracks. 3. You're right, I'm looking for a method like moving average, I've tried the post suggested by Bob, in fact I post that message that Bob was referring to. However, this post shows how to smooth aircraft tracks, David Bitner used very nicely 4 variables into his code, but my tracks just have two columns, this is, longitude and latitude. So, I tried this code with no success so far. I already have installed PL/R and seems to be working with my PostGis after some examples. 4. After smoothing, I want to convert my tracks into points, this is, one track divide it into regular points and store it in regard to the name's track. But first I need to smooth the tracks :) -- View this message in context: http://postgis.17.n6.nabble.com/navigation-tracks-x-zy-h-tp4687808p4689698.html Sent from the PostGIS - User mailing list archive at Nabble.com. ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
Re: [postgis-users] navigation tracks (x,zy) h
Thanks Bob, in fact I posted that recently to the list, it is exactly what I'm looking for, the output image posted by David Bitner reflects exactly my problem, however he used 4 parameters for this process, but I only have two, i.e., longitude and latitude in geographical coordinates (WGS84). So, I haven't success in reproducing what he did. I installed R and PL/R already and seems to be working with my PostGis. -- View this message in context: http://postgis.17.n6.nabble.com/navigation-tracks-x-zy-h-tp4687808p4689709.html Sent from the PostGIS - User mailing list archive at Nabble.com. ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
Re: [postgis-users] navigation tracks (x,zy) h
I didn't read into the solution he provided, so I don't know the background of the parameters he's using, but I would suspect that are intended for R to use as some sort of smoothing config. Unless I'm mis reading your reply. I've never used R myself, but I have looked at it's capabilities, and they look very interesting. bobb Gery gameji...@hotmail.com wrote: Thanks Bob, in fact I posted that recently to the list, it is exactly what I'm looking for, the output image posted by David Bitner reflects exactly my problem, however he used 4 parameters for this process, but I only have two, i.e., longitude and latitude in geographical coordinates (WGS84). So, I haven't success in reproducing what he did. I installed R and PL/R already and seems to be working with my PostGis. -- View this message in context: http://postgis.17.n6.nabble.com/navigation-tracks-x-zy-h-tp4687808p4689709.html Sent from the PostGIS - User mailing list archive at Nabble.com. ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
Re: [postgis-users] navigation tracks (x,zy) h
select distinct lat, lon unless I misunderstood the problem... steve Gery gameji...@hotmail.com@postgis.refractions.net Envoyé par : postgis-users-boun...@postgis.refractions.net 2012-04-05 11:15 Veuillez répondre à PostGIS Users Discussion postgis-users@postgis.refractions.net A postgis-users@postgis.refractions.net cc Objet Re: [postgis-users] navigation tracks (x,zy) h Thanks Steve, let me explain you a bit the problem: 1. the geographical positions of these tracks were recorded with a device without enough precision, this is, instead of having for example: 67.534221 9.988212 67.534223 9.988210 67.534225 9.988207 I'm getting: 67.534100 9.987500 67.534100 9.987500 67.534100 9.987500 So I need to smooth such thing. 2. I tried st_simplify, but the problem is that this method doesn't produce curves, I tried many values but at the end I just could get a straight line, and that doesn't reflect the curves of these tracks. 3. You're right, I'm looking for a method like moving average, I've tried the post suggested by Bob, in fact I post that message that Bob was referring to. However, this post shows how to smooth aircraft tracks, David Bitner used very nicely 4 variables into his code, but my tracks just have two columns, this is, longitude and latitude. So, I tried this code with no success so far. I already have installed PL/R and seems to be working with my PostGis after some examples. 4. After smoothing, I want to convert my tracks into points, this is, one track divide it into regular points and store it in regard to the name's track. But first I need to smooth the tracks :) -- View this message in context: http://postgis.17.n6.nabble.com/navigation-tracks-x-zy-h-tp4687808p4689698.html Sent from the PostGIS - User mailing list archive at Nabble.com. ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
Re: [postgis-users] Importing timezone shape files - newbie
Can someone explain the difference between the two types of shape files and the advantages/disadvantages of each in more general terms? As far as I understandd from http://efele.net/maps/tz/world/: and a TZ timezone will sometimes have multiple polygons. for the first file, and and there is a single geometry for each TZ timezone for the second one. The big shapefile contains one multipolygon by timezone and by country, the small one contains one polygon for each part of a country (islands for instance) for each timezone, thus the count difference . nicolas ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
Re: [postgis-users] Importing timezone shape files - newbie
What does that mean in use? I'm not familiar with this stuff yet. :) On Thu, Apr 5, 2012 at 9:41 AM, Nicolas Ribot nicolas.ri...@gmail.com wrote: Can someone explain the difference between the two types of shape files and the advantages/disadvantages of each in more general terms? As far as I understandd from http://efele.net/maps/tz/world/: and a TZ timezone will sometimes have multiple polygons. for the first file, and and there is a single geometry for each TZ timezone for the second one. The big shapefile contains one multipolygon by timezone and by country, the small one contains one polygon for each part of a country (islands for instance) for each timezone, thus the count difference . nicolas ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
Re: [postgis-users] ERROR: Geometry has Z dimension but column does not
My apologies, I sent the previous post in a hurry. Below is the query I am
using to insert a geography from text. I still receive the error indicated. I
am, in fact, looking to insert a geography with a Z value.
CREATE TABLE myTable(location geography(PointZ,4326));
INSERT INTO myTable (location) VALUES
(st_geographyFromText('SRID=4326;PointZ(85 35 500)'));
ERROR: Geometry has Z dimension but column does not
** Error **
ERROR: Geometry has Z dimension but column does not
SQL state: 22023
--Jack Gold
___
postgis-users mailing list
postgis-users@postgis.refractions.net
http://postgis.refractions.net/mailman/listinfo/postgis-users
Re: [postgis-users] ERROR: Geometry has Z dimension but column does not
On Thu, Apr 05, 2012 at 01:53:59PM -0400, Gold, Jack L (US SSA) wrote:
My apologies, I sent the previous post in a hurry. Below is the query I am
using to insert a geography from text. I still receive the error indicated.
I am, in fact, looking to insert a geography with a Z value.
CREATE TABLE myTable(location geography(PointZ,4326));
INSERT INTO myTable (location) VALUES
(st_geographyFromText('SRID=4326;PointZ(85 35 500)'));
ERROR: Geometry has Z dimension but column does not
Works for me. What's the result of : SELECT Postgis_full_version() ?
--strk;
,--o-.
| __/ |Delivering high quality PostGIS 2.0 !
| / 2.0 |http://strk.keybit.net - http://vizzuality.com
`-o--'
___
postgis-users mailing list
postgis-users@postgis.refractions.net
http://postgis.refractions.net/mailman/listinfo/postgis-users
Re: [postgis-users] ERROR: Geometry has Z dimension but column does not
On Thu, Apr 5, 2012 at 11:15 AM, Sandro Santilli s...@keybit.net wrote:
On Thu, Apr 05, 2012 at 01:53:59PM -0400, Gold, Jack L (US SSA) wrote:
My apologies, I sent the previous post in a hurry. Below is the query I am
using to insert a geography from text. I still receive the error indicated.
I am, in fact, looking to insert a geography with a Z value.
CREATE TABLE myTable(location geography(PointZ,4326));
INSERT INTO myTable (location) VALUES
(st_geographyFromText('SRID=4326;PointZ(85 35 500)'));
ERROR: Geometry has Z dimension but column does not
Works for me. What's the result of : SELECT Postgis_full_version() ?
It does? Doesn't for me! working this now.
P
--strk;
,--o-.
| __/ | Delivering high quality PostGIS 2.0 !
| / 2.0 | http://strk.keybit.net - http://vizzuality.com
`-o--'
___
postgis-users mailing list
postgis-users@postgis.refractions.net
http://postgis.refractions.net/mailman/listinfo/postgis-users
___
postgis-users mailing list
postgis-users@postgis.refractions.net
http://postgis.refractions.net/mailman/listinfo/postgis-users
Re: [postgis-users] ERROR: Geometry has Z dimension but column does not
Working here. It's a problem with the st_geographyFromText somehow,
you can insert values into the table if you feed it text directly.
INSERT INTO myTable (location) VALUES ('SRID=4326;PointZ(85 35 500)');
P
On Thu, Apr 5, 2012 at 11:16 AM, Paul Ramsey pram...@opengeo.org wrote:
On Thu, Apr 5, 2012 at 11:15 AM, Sandro Santilli s...@keybit.net wrote:
On Thu, Apr 05, 2012 at 01:53:59PM -0400, Gold, Jack L (US SSA) wrote:
My apologies, I sent the previous post in a hurry. Below is the query I am
using to insert a geography from text. I still receive the error
indicated. I am, in fact, looking to insert a geography with a Z value.
CREATE TABLE myTable(location geography(PointZ,4326));
INSERT INTO myTable (location) VALUES
(st_geographyFromText('SRID=4326;PointZ(85 35 500)'));
ERROR: Geometry has Z dimension but column does not
Works for me. What's the result of : SELECT Postgis_full_version() ?
It does? Doesn't for me! working this now.
P
--strk;
,--o-.
| __/ | Delivering high quality PostGIS 2.0 !
| / 2.0 | http://strk.keybit.net - http://vizzuality.com
`-o--'
___
postgis-users mailing list
postgis-users@postgis.refractions.net
http://postgis.refractions.net/mailman/listinfo/postgis-users
___
postgis-users mailing list
postgis-users@postgis.refractions.net
http://postgis.refractions.net/mailman/listinfo/postgis-users
Re: [postgis-users] ERROR: Geometry has Z dimension but column does not
Il giorno gio, 05/04/2012 alle 11.16 -0700, Paul Ramsey ha scritto:
On Thu, Apr 5, 2012 at 11:15 AM, Sandro Santilli s...@keybit.net wrote:
On Thu, Apr 05, 2012 at 01:53:59PM -0400, Gold, Jack L (US SSA) wrote:
My apologies, I sent the previous post in a hurry. Below is the query I
am using to insert a geography from text. I still receive the error
indicated. I am, in fact, looking to insert a geography with a Z value.
CREATE TABLE myTable(location geography(PointZ,4326));
INSERT INTO myTable (location) VALUES
(st_geographyFromText('SRID=4326;PointZ(85 35 500)'));
ERROR: Geometry has Z dimension but column does not
Works for me. What's the result of : SELECT Postgis_full_version() ?
It does? Doesn't for me! working this now.
P
Same problem here.
I get: ERROR: Geometry has Z dimension but column does not
-SL
___
postgis-users mailing list
postgis-users@postgis.refractions.net
http://postgis.refractions.net/mailman/listinfo/postgis-users
Re: [postgis-users] ERROR: Geometry has Z dimension but column does not
Fixed.
http://trac.osgeo.org/postgis/ticket/1755
On Thu, Apr 5, 2012 at 11:23 AM, Salvatore Larosa lrssv...@gmail.com wrote:
Il giorno gio, 05/04/2012 alle 11.16 -0700, Paul Ramsey ha scritto:
On Thu, Apr 5, 2012 at 11:15 AM, Sandro Santilli s...@keybit.net wrote:
On Thu, Apr 05, 2012 at 01:53:59PM -0400, Gold, Jack L (US SSA) wrote:
My apologies, I sent the previous post in a hurry. Below is the query I
am using to insert a geography from text. I still receive the error
indicated. I am, in fact, looking to insert a geography with a Z value.
CREATE TABLE myTable(location geography(PointZ,4326));
INSERT INTO myTable (location) VALUES
(st_geographyFromText('SRID=4326;PointZ(85 35 500)'));
ERROR: Geometry has Z dimension but column does not
Works for me. What's the result of : SELECT Postgis_full_version() ?
It does? Doesn't for me! working this now.
P
Same problem here.
I get: ERROR: Geometry has Z dimension but column does not
-SL
___
postgis-users mailing list
postgis-users@postgis.refractions.net
http://postgis.refractions.net/mailman/listinfo/postgis-users
___
postgis-users mailing list
postgis-users@postgis.refractions.net
http://postgis.refractions.net/mailman/listinfo/postgis-users
Re: [postgis-users] ERROR: Geometry has Z dimension but column does not
I'm beginning to think the problem is actually a corrupted database. I don't
even have a geometry_columns table anymore. The SELECT Postgis_full_version()
results in postgis_libjson_version() error shown below. I'm going to call it a
corrupted database and rebuild from scratch. Thanks for the help Sandro.
ERROR: function postgis_libjson_version() does not exist
LINE 1: SELECT postgis_libjson_version()
^
HINT: No function matches the given name and argument types. You might need to
add explicit type casts.
QUERY: SELECT postgis_libjson_version()
CONTEXT: PL/pgSQL function postgis_full_version line 20 at SQL statement
** Error **
ERROR: function postgis_libjson_version() does not exist
SQL state: 42883
Hint: No function matches the given name and argument types. You might need to
add explicit type casts.
Context: PL/pgSQL function postgis_full_version line 20 at SQL statement
--Jack
-Original Message-
From: postgis-users-boun...@postgis.refractions.net
[mailto:postgis-users-boun...@postgis.refractions.net] On Behalf Of Sandro
Santilli
Sent: Thursday, April 05, 2012 2:15 PM
To: PostGIS Users Discussion
Subject: Re: [postgis-users] ERROR: Geometry has Z dimension but column does not
On Thu, Apr 05, 2012 at 01:53:59PM -0400, Gold, Jack L (US SSA) wrote:
My apologies, I sent the previous post in a hurry. Below is the query I am
using to insert a geography from text. I still receive the error indicated.
I am, in fact, looking to insert a geography with a Z value.
CREATE TABLE myTable(location geography(PointZ,4326));
INSERT INTO myTable (location) VALUES
(st_geographyFromText('SRID=4326;PointZ(85 35 500)'));
ERROR: Geometry has Z dimension but column does not
Works for me. What's the result of : SELECT Postgis_full_version() ?
--strk;
,--o-.
| __/ |Delivering high quality PostGIS 2.0 !
| / 2.0 |http://strk.keybit.net - http://vizzuality.com
`-o--'
___
postgis-users mailing list
postgis-users@postgis.refractions.net
http://postgis.refractions.net/mailman/listinfo/postgis-users
___
postgis-users mailing list
postgis-users@postgis.refractions.net
http://postgis.refractions.net/mailman/listinfo/postgis-users
Re: [postgis-users] ERROR: Geometry has Z dimension but column does not
Il giorno gio, 05/04/2012 alle 11.25 -0700, Paul Ramsey ha scritto: Fixed. http://trac.osgeo.org/postgis/ticket/1755 Sorry, I had escaped. Now everything works fine! Cheers, -SL ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
Re: [postgis-users] summarizing a polygon values in a raster
Thanks for your suggestion Pierre. I am working with v2.0 and it seems that ST_PixelsAsPolygons() is not available. The singular Pixel version works though. Any idea why that would be? Peter On Wed, Apr 4, 2012 at 7:31 AM, Pierre Racine pierre.rac...@sbf.ulaval.cawrote: I would like to create a raster of an area-weighted average value derived from a polygon layer. The raster template is a 2km grid and the polygon layer is also a grid but at 10km. If some one could recommend a (high level) strategy for this if be grateful. 1) Create a vector grid having a x and a y like this: CREATE TABLE yourgrid AS SELECT (gvxy).geom geom, (gvxy).x x, (gvxy).y y FROM (SELECT ST_PixelsAsPolygons(rast) gvxy FROM (SELECT ST_MakeEmptyRaster(your own parameters here covering your polygon extent)) foo1) foo2 2) Make sure your polygon layer is indexed CREATE INDEX yourpolylayer_geom_idx ON yourpolylayer USING gist (geom ); 3) Intersect mygrid with your polygon layer and generate summary stats at the same time: CREATE TABLE weightedvectorgrid AS SELECT g.x, g.y, g.geom, (aws).weightedmean mean FROM (SELECT ST_AreaWeightedSummaryStats(intgeom, p.value) aws, g.x, g.y, g.geom FROM (SELECT ST_Intersection(g.geom, p.geom) intgeom, p.value, g.x, g.y, g.geom FROM yourgrid g, yourpolylayer p WHERE ST_Intersects(g.geom, p.geom) ) foo GROUP BY g.x, g.y, g.geom ) foo2 The ST_AreaWeightedSummaryStats() function is not part of the PostGIS release but is available as part of the source tree: http://trac.osgeo.org/postgis/browser/trunk/raster/scripts/plpgsql/st_areaweightedsummarystats.sql 4) Union all the geometry as a raster (this one might be a bit slow depending on the size of your grid) CREATE TABLE weightedrastergrid AS SELECT ST_Union(ST_AsRaster(geom, 'MEAN')) rast FROM weightedvectorgrid I wrote all these without testing so you might have some adjustment to do. Let me know about your progress... Pierre ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
Re: [postgis-users] summarizing a polygon values in a raster
Thanks for your suggestion Pierre. I am working with v2.0 and it seems that ST_PixelsAsPolygons() is not available. The singular Pixel version works though. Any idea why that would be? Try ST_PixelAsPolygons()... (without the firs 's') ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
Re: [postgis-users] Importing timezone shape files - newbie
Scott, It's greyed out if you don't have a database selected. As long as your database has postgis installed, it should work fine. Regarding world files as I recall, I think the 394 one has one record per TZ, and the other has further broken out. I usually use the 28,000 one for spatial queries since its a bit more efficient because it has smaller polygons. Good luck :) Leo http://www.postgis.us -Original Message- From: postgis-users-boun...@postgis.refractions.net [mailto:postgis-users-boun...@postgis.refractions.net] On Behalf Of Scott Chapman Sent: Wednesday, April 04, 2012 6:07 PM To: postgis-users@postgis.refractions.net Subject: [postgis-users] Importing timezone shape files - newbie Hi all, I'm using PG 9.1.3 with pgAdmin 1.14.2 on Windows 7 Home Edition to get familiar with PostGIS and TimeZone data. PostGIS 1.5.3 is installed and the postgis database was created by the installer. My immediate end goal is to be able to take a Lat/Lon and query PG to find the correct time zone for the location. I installed the PostGIS Shapefile and DBF loader plugin in pgAdmin but it is greyed out in the Plugins menu. I found this site with the shape files: http://efele.net/maps/tz/world/ It has two shape files: The tz_world shapefile captures the boundaries of the TZ timezones across the world, as of TZ 2011b. The geometries are all POLYGONs, and a TZ timezone will sometimes have multiple polygons. There are about 28,000 rows. The tz_world_mp shapefile captures the same boundaries. The geometries are either POLYGONs or MULTIPOLYGONs, and there is a single geometry for each TZ timezone. There are 394 rows. First question is, Which shape file to use. I don't understand the difference between the two and the merits of each. Secondly, clues on how to import this data correctly. Any other pointers are welcome! I'm not sure yet, how to do the SQL to make the query. Thanks! Scott ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
[postgis-users] Severe shapefile upload issues
I am working with postgis 2.0.0 and have found several issues when trying to
upload shapefiles into a postgis database.
The most pressing issue I have found is that when setting up the postgis
extension in a schema other than public, AddGeometryColumn() completely
fails to do anything.
For example I used shp2pgsql to try to test upload a shapefile using the
following command:
shp2pgsql -c -s 2277:4326 -i -I C:\tcad2\20120301_TCAD SHAPEFILES\MUD.shp
postgis.mudtest | psql ...options
which creates the following sql:
SET CLIENT_ENCODING TO UTF8;
SET STANDARD_CONFORMING_STRINGS TO ON;
BEGIN;
CREATE TABLE postgis.mudtest (gid serial,
objectid int4,
tcmud_name varchar(50),
shape_area numeric,
shape_len numeric);
ALTER TABLE postgis.mudtest ADD PRIMARY KEY (gid);
SELECT
AddGeometryColumn('postgis','mudtest','geom','4326','MULTIPOLYGON',2);
INSERT INTO postgis.mudtest
(objectid,tcmud_name,shape_area,shape_len,geom) VALUES
('14','LAKEWAY
MUD','9.60083055833e+004','1.24005179036e+003',ST_Transform('010620E508010001030001000500E03113F38C20474188DA1ADB9142634140726A6B94204741B07B4EE669426341007FCF91FE1F4741D0BE4D2668426341A03E7819F71F474190631C1B90426341E03113F38C20474188DA1ADB91426341',
4326));
CREATE INDEX mudtest_geom_gist ON postgis.mudtest USING GIST (geom);
COMMIT;
When AddGeometryColumn is run, I receive the following error:
ERROR: function addgeometrycolumn(unknown, character varying, character
varying, character varying, integer, character varying, integer, boolean)
does not exist
LINE 1: SELECT AddGeometryColumn('',$1,$2,$3,$4,$5,$6,$7)
^
HINT: No function matches the given name and argument types. You might need
to add explicit type casts.
QUERY: SELECT AddGeometryColumn('',$1,$2,$3,$4,$5,$6,$7)
CONTEXT: PL/pgSQL function addgeometrycolumn line 5 at SQL statement
** Error **
ERROR: function addgeometrycolumn(unknown, character varying, character
varying, character varying, integer, character varying, integer, boolean)
does not exist
SQL state: 42883
Hint: No function matches the given name and argument types. You might need
to add explicit type casts.
Context: PL/pgSQL function addgeometrycolumn line 5 at SQL statement
I verified that the function does in fact exist within the postgis schema
and then even tried explicitly casting the arguments in the following way so
that they would match exactly:
SELECT
postgis.AddGeometryColumn('postgis'::varchar,'mudtest'::varchar,'geom'::varchar,4326,'MULTIPOLYGON'::varchar,2
true);
but I had no luck, and when I tested in another database where the postgis
extension is installed in public it worked without complaint.
I created the postgis extensions in my first database as follows:
create schema postgis;
create extension postgis with schema postgis;
GRANT ALL ON SCHEMA postgis TO postgres;
GRANT ALL ON SCHEMA postgis TO public;
set search_path to postgis, $user,public
I figured this would pretty much take care of everything that could possibly
cause any problemes, but I horribly mistaken in that regard.
-
The second issue comes with the srid conversion itself --even in cases when
I import to a database where the postgis extension is in the public schema.
I receive the following error:
ERROR: function st_transform(unknown, integer) is not unique
LINE 11: ...AY MUD','9.60083055833e+004','1.24005179036e+003',ST_Transfo...
^
HINT: Could not choose a best candidate function. You might need to add
explicit type casts.
** Error **
ERROR: function st_transform(unknown, integer) is not unique
SQL state: 42725
Hint: Could not choose a best candidate function. You might need to add
explicit type casts.
Character: 495
In this case it appears that the geometry field is simply not being
recognized as such and thus confusing the function.
Does anyone know why these issues are occurring and how I might fix them?
I realize I could just programatically edit the sql to bypass using
AddGeometryColumn and then add in
::geometry(MULTIPOLYGON,srid) after the geometry string and then upload the
edited sql into the database but I am hoping there is a better .
If anyone has a script that does this already (in a fast efficient
manner)please let me know! Otherwise, I will just write one in python.
As a side note, I noticed that the gui shapefile uploader no longer has the
srid convert option, so I am wondering if this feature has been deprecated
since it does not work and the documentation has just not been updated to
reflect this change.
I will also note that if I upload a shapefile into a database with the
postgis extension set to public and then alter the extenion to postgis, most
of the postgis functions seem to work fine.
Thanks!
THX1138
--
View this message in context:
Re: [postgis-users] Severe shapefile upload issues
You'll want to make sure that the postgis schema is in the user
account's search_path.
-bborie
On 04/05/2012 03:05 PM, THX1138 wrote:
I am working with postgis 2.0.0 and have found several issues when trying to
upload shapefiles into a postgis database.
The most pressing issue I have found is that when setting up the postgis
extension in a schema other than public, AddGeometryColumn() completely
fails to do anything.
For example I used shp2pgsql to try to test upload a shapefile using the
following command:
shp2pgsql -c -s 2277:4326 -i -I C:\tcad2\20120301_TCAD SHAPEFILES\MUD.shp
postgis.mudtest | psql ...options
which creates the following sql:
SET CLIENT_ENCODING TO UTF8;
SET STANDARD_CONFORMING_STRINGS TO ON;
BEGIN;
CREATE TABLE postgis.mudtest (gid serial,
objectid int4,
tcmud_name varchar(50),
shape_area numeric,
shape_len numeric);
ALTER TABLE postgis.mudtest ADD PRIMARY KEY (gid);
SELECT
AddGeometryColumn('postgis','mudtest','geom','4326','MULTIPOLYGON',2);
INSERT INTO postgis.mudtest
(objectid,tcmud_name,shape_area,shape_len,geom) VALUES
('14','LAKEWAY
MUD','9.60083055833e+004','1.24005179036e+003',ST_Transform('010620E508010001030001000500E03113F38C20474188DA1ADB9142634140726A6B94204741B07B4EE669426341007FCF91FE1F4741D0BE4D2668426341A03E7819F71F474190631C1B90426341E03113F38C20474188DA1ADB91426341',
4326));
CREATE INDEX mudtest_geom_gist ON postgis.mudtest USING GIST (geom);
COMMIT;
When AddGeometryColumn is run, I receive the following error:
ERROR: function addgeometrycolumn(unknown, character varying, character
varying, character varying, integer, character varying, integer, boolean)
does not exist
LINE 1: SELECT AddGeometryColumn('',$1,$2,$3,$4,$5,$6,$7)
^
HINT: No function matches the given name and argument types. You might need
to add explicit type casts.
QUERY: SELECT AddGeometryColumn('',$1,$2,$3,$4,$5,$6,$7)
CONTEXT: PL/pgSQL function addgeometrycolumn line 5 at SQL statement
** Error **
ERROR: function addgeometrycolumn(unknown, character varying, character
varying, character varying, integer, character varying, integer, boolean)
does not exist
SQL state: 42883
Hint: No function matches the given name and argument types. You might need
to add explicit type casts.
Context: PL/pgSQL function addgeometrycolumn line 5 at SQL statement
I verified that the function does in fact exist within the postgis schema
and then even tried explicitly casting the arguments in the following way so
that they would match exactly:
SELECT
postgis.AddGeometryColumn('postgis'::varchar,'mudtest'::varchar,'geom'::varchar,4326,'MULTIPOLYGON'::varchar,2
true);
but I had no luck, and when I tested in another database where the postgis
extension is installed in public it worked without complaint.
I created the postgis extensions in my first database as follows:
create schema postgis;
create extension postgis with schema postgis;
GRANT ALL ON SCHEMA postgis TO postgres;
GRANT ALL ON SCHEMA postgis TO public;
set search_path to postgis, $user,public
I figured this would pretty much take care of everything that could possibly
cause any problemes, but I horribly mistaken in that regard.
-
The second issue comes with the srid conversion itself --even in cases when
I import to a database where the postgis extension is in the public schema.
I receive the following error:
ERROR: function st_transform(unknown, integer) is not unique
LINE 11: ...AY MUD','9.60083055833e+004','1.24005179036e+003',ST_Transfo...
^
HINT: Could not choose a best candidate function. You might need to add
explicit type casts.
** Error **
ERROR: function st_transform(unknown, integer) is not unique
SQL state: 42725
Hint: Could not choose a best candidate function. You might need to add
explicit type casts.
Character: 495
In this case it appears that the geometry field is simply not being
recognized as such and thus confusing the function.
Does anyone know why these issues are occurring and how I might fix them?
I realize I could just programatically edit the sql to bypass using
AddGeometryColumn and then add in
::geometry(MULTIPOLYGON,srid) after the geometry string and then upload the
edited sql into the database but I am hoping there is a better .
If anyone has a script that does this already (in a fast efficient
manner)please let me know! Otherwise, I will just write one in python.
As a side note, I noticed that the gui shapefile uploader no longer has the
srid convert option, so I am wondering if this feature has been deprecated
since it does not work and the documentation has just not been updated to
reflect this change.
I will also note that if I upload a shapefile into a database with the
postgis extension set to public and then alter the extenion to postgis, most
of the
Re: [postgis-users] Severe shapefile upload issues
It sounds like you might have a mix of postgis installs. Did you upgrade
from a prior version and how did you do it?
What does
SELECT postgis_full_verion();
return.
I just moved my postgis extension from public to postgis schema. Added
postgis to my search path and then imported a shape file with the gui loader
and had no issues.
Thanks,
Regina
http://www.postgis.us
-Original Message-
From: postgis-users-boun...@postgis.refractions.net
[mailto:postgis-users-boun...@postgis.refractions.net] On
Behalf Of THX1138
Sent: Thursday, April 05, 2012 6:06 PM
To: postgis-users@postgis.refractions.net
Subject: [postgis-users] Severe shapefile upload issues
I am working with postgis 2.0.0 and have found several issues
when trying to upload shapefiles into a postgis database.
The most pressing issue I have found is that when setting up
the postgis extension in a schema other than public,
AddGeometryColumn() completely fails to do anything.
For example I used shp2pgsql to try to test upload a
shapefile using the following command:
shp2pgsql -c -s 2277:4326 -i -I C:\tcad2\20120301_TCAD
SHAPEFILES\MUD.shp
postgis.mudtest | psql ...options
which creates the following sql:
SET CLIENT_ENCODING TO UTF8;
SET STANDARD_CONFORMING_STRINGS TO ON;
BEGIN;
CREATE TABLE postgis.mudtest (gid serial, objectid
int4, tcmud_name varchar(50), shape_area numeric,
shape_len numeric); ALTER TABLE postgis.mudtest ADD
PRIMARY KEY (gid); SELECT
AddGeometryColumn('postgis','mudtest','geom','4326','MULTIPOLYGON',2);
INSERT INTO postgis.mudtest
(objectid,tcmud_name,shape_area,shape_len,geom)
VALUES ('14','LAKEWAY
MUD','9.60083055833e+004','1.24005179036e+003',ST_Transform('0
10620E508010001030001000500E03113F38C20474188DA1ADB9
142634140726A6B94204741B07B4EE669426341007FCF91FE1F4741D0BE4D2668426341A03E
7819F71F474190631C1B90426341E03113F38C20474188DA1ADB91426341',
4326));
CREATE INDEX mudtest_geom_gist ON postgis.mudtest USING
GIST (geom); COMMIT;
When AddGeometryColumn is run, I receive the following error:
ERROR: function addgeometrycolumn(unknown, character
varying, character varying, character varying, integer,
character varying, integer, boolean) does not exist LINE 1:
SELECT AddGeometryColumn('',$1,$2,$3,$4,$5,$6,$7)
^
HINT: No function matches the given name and argument types.
You might need to add explicit type casts.
QUERY: SELECT AddGeometryColumn('',$1,$2,$3,$4,$5,$6,$7)
CONTEXT: PL/pgSQL function addgeometrycolumn line 5 at SQL
statement
** Error **
ERROR: function addgeometrycolumn(unknown, character varying,
character varying, character varying, integer, character
varying, integer, boolean) does not exist SQL state: 42883
Hint: No function matches the given name and argument types.
You might need to add explicit type casts.
Context: PL/pgSQL function addgeometrycolumn line 5 at SQL statement
I verified that the function does in fact exist within the
postgis schema and then even tried explicitly casting the
arguments in the following way so that they would match exactly:
SELECT
postgis.AddGeometryColumn('postgis'::varchar,'mudtest'::varcha
r,'geom'::varchar,4326,'MULTIPOLYGON'::varchar,2
true);
but I had no luck, and when I tested in another database
where the postgis extension is installed in public it worked
without complaint.
I created the postgis extensions in my first database as follows:
create schema postgis;
create extension postgis with schema postgis; GRANT ALL ON
SCHEMA postgis TO postgres; GRANT ALL ON SCHEMA postgis TO
public; set search_path to postgis, $user,public
I figured this would pretty much take care of everything that
could possibly cause any problemes, but I horribly mistaken
in that regard.
--
---
The second issue comes with the srid conversion itself --even
in cases when I import to a database where the postgis
extension is in the public schema.
I receive the following error:
ERROR: function st_transform(unknown, integer) is not unique
LINE 11: ...AY
MUD','9.60083055833e+004','1.24005179036e+003',ST_Transfo...
^
HINT: Could not choose a best candidate function. You might
need to add explicit type casts.
** Error **
ERROR: function st_transform(unknown, integer) is not unique
SQL state: 42725
Hint: Could not choose a best candidate function. You might
need to add explicit type casts.
Character: 495
In this case it appears that the geometry field is simply not
being recognized as such and thus confusing the function.
Does anyone know why these issues are occurring and how I
might fix them?
I realize I could just programatically edit the sql to
bypass using AddGeometryColumn
Re: [postgis-users] Severe shapefile upload issues
He has that it seems in fact he has it in the first slot since all his test
tables are being created in postgis. It should be at the end.
The AddGeometryColumn error and non-unique would happen if you have some
obsolete functions in your install. All these would be in public if they
are from an older postgis install.
What you can do THX -- I'm pretty sure your install is dirty. Is run the
drop function statements that are in the postgis_upgrade_minor.sql.
Make sure to set your search_path to public so it only removes the old ones
before you run the drop statements.
You'll see the drops at the end of the script: postgis_upgrade_minor.sql
That will get rid of the functions you have that shouldn't be there.
-Original Message-
From: postgis-users-boun...@postgis.refractions.net
[mailto:postgis-users-boun...@postgis.refractions.net] On
Behalf Of Bborie Park
Sent: Thursday, April 05, 2012 6:47 PM
To: postgis-users@postgis.refractions.net
Subject: Re: [postgis-users] Severe shapefile upload issues
You'll want to make sure that the postgis schema is in the
user account's search_path.
-bborie
On 04/05/2012 03:05 PM, THX1138 wrote:
I am working with postgis 2.0.0 and have found several issues when
trying to upload shapefiles into a postgis database.
The most pressing issue I have found is that when setting up the
postgis extension in a schema other than public,
AddGeometryColumn()
completely fails to do anything.
For example I used shp2pgsql to try to test upload a
shapefile using
the following command:
shp2pgsql -c -s 2277:4326 -i -I C:\tcad2\20120301_TCAD
SHAPEFILES\MUD.shp
postgis.mudtest | psql ...options
which creates the following sql:
SET CLIENT_ENCODING TO UTF8;
SET STANDARD_CONFORMING_STRINGS TO ON; BEGIN; CREATE TABLE
postgis.mudtest (gid serial, objectid int4, tcmud_name
varchar(50), shape_area numeric, shape_len numeric);
ALTER TABLE
postgis.mudtest ADD PRIMARY KEY (gid); SELECT
AddGeometryColumn('postgis','mudtest','geom','4326','MULTIPOLYGON',2);
INSERT INTO postgis.mudtest
(objectid,tcmud_name,shape_area,shape_len,geom) VALUES
('14','LAKEWAY
MUD','9.60083055833e+004','1.24005179036e+003',ST_Transform('01062
0E508010001030001000500E03113F38C20474188DA1ADB914
2634140726A6B94204741B07B4EE669426341007FCF91FE1F4741D0BE4D2668426341A
03E7819F71F474190631C1B90426341E03113F38C20474188DA1ADB91426341',
4326));
CREATE INDEX mudtest_geom_gist ON postgis.mudtest USING GIST
(geom); COMMIT;
When AddGeometryColumn is run, I receive the following error:
ERROR: function addgeometrycolumn(unknown, character varying,
character varying, character varying, integer, character varying,
integer, boolean) does not exist LINE 1: SELECT
AddGeometryColumn('',$1,$2,$3,$4,$5,$6,$7)
^
HINT: No function matches the given name and argument types. You
might need to add explicit type casts.
QUERY: SELECT AddGeometryColumn('',$1,$2,$3,$4,$5,$6,$7)
CONTEXT: PL/pgSQL function addgeometrycolumn line 5 at SQL
statement
** Error **
ERROR: function addgeometrycolumn(unknown, character varying,
character varying, character varying, integer, character varying,
integer, boolean) does not exist SQL state: 42883
Hint: No function matches the given name and argument
types. You might
need to add explicit type casts.
Context: PL/pgSQL function addgeometrycolumn line 5 at
SQL statement
I verified that the function does in fact exist within the postgis
schema and then even tried explicitly casting the arguments in the
following way so that they would match exactly:
SELECT
postgis.AddGeometryColumn('postgis'::varchar,'mudtest'::varchar,'geom'
::varchar,4326,'MULTIPOLYGON'::varchar,2
true);
but I had no luck, and when I tested in another database where the
postgis extension is installed in public it worked without
complaint.
I created the postgis extensions in my first database as follows:
create schema postgis;
create extension postgis with schema postgis; GRANT ALL ON SCHEMA
postgis TO postgres; GRANT ALL ON SCHEMA postgis TO public; set
search_path to postgis, $user,public
I figured this would pretty much take care of everything that could
possibly cause any problemes, but I horribly mistaken in
that regard.
--
---
The second issue comes with the srid conversion itself
--even in cases
when I import to a database where the postgis extension is
in the public schema.
I receive the following error:
ERROR: function st_transform(unknown, integer) is not
unique LINE 11:
...AY MUD','9.60083055833e+004','1.24005179036e+003',ST_Transfo...
^
HINT: Could not choose a best
