[postgis-users] pgsql2shp
Hello, When I run pgsql2shp like so: pgsql2shp -u plm25 -g the_geom database table I get the following output: Xcolumn number -1 is out of range 0..1 column number -1 is out of range 0..1 column number -1 is out of range 0..1 column number -1 is out of range 0..1 column number -1 is out of range 0..1 column number -1 is out of range 0..1 column number -1 is out of range 0..1 column number -1 is out of range 0..1 column number -1 is out of range 0..1 column number -1 is out of range 0..1 column number -1 is out of range 0..1 column number -1 is out of range 0..1 This continues in blocks for (I assume) each row in the table. When I go to load the shape I get null values for all rows as follows: INSERT INTO table (the_geom) VALUES (NULL); My versions are: PostgreSQL 9.1.4 on sparc-sun-solaris2.10, compiled by cc: Sun C 5.11 SunOS_sparc 145354-05 2011/09/13, 64-bit POSTGIS=2.0.1 r9979 GEOS=3.3.5-CAPI-1.7.5 PROJ=Rel. 4.8.0, 6 March 2012 GDAL=GDAL 1.9.1, released 2012/05/15 GDAL_DATA not found LIBXML=2.7.8 LIBJSON=UNKNOWN R ASTER Any ideas? This is a fresh table, not migrated form an old version. Cheers, James Sewell Solutions Architect LISAsoft The contents of this email are confidential and may be subject to legal or professional privilege and copyright. No representation is made that this email is free of viruses or other defects. If you have received this communication in error, you may not copy or distribute any part of it or otherwise disclose its contents to anyone. Please advise the sender of your incorrect receipt of this correspondence. ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
Re: [postgis-users] ArcGIS and PostGIS feedback
Duarte, Your experience is pretty well identical to nearly 20 years of experience with Oracle and ArcSDE. Many times, since SDE 2.0 (first port from Interbase to Oracle after ESRI purchase), I have had cause to turn on SQL Tracing and then blanch at the SQL the ESRI software generates. The ESRI claims of the poor performance of Oracle Sdo_Geometry with ArcSDE being due to Oracle are impossible to expose because of the ban Oracle has on comparing their database with any other database or associated product and publishing the results. Many practitioners like yourself and myself have dropped the SQL and discovered the probable cause of the performance issues. But it doesn't matter when the world's dominant GIS company says the problems are otherwise. The recent publication of a PostGIS vs Oracle performance benchmark on LinkedIn is a case in point. (The methodology was so bad that the comparison was worth nothing. Oracle, to their credit, then contacted the University in question and offered some advice.) I predicted that performance issues would occur when ESRI moved to support PostgreSQL/PostGIS. Looks like it has come true. As I have indicated in other emails. If you want clean data-level integration don't use the whole GeoDatabase framework - don't use ESRI's versioning. If you have to use it, find open products that use the right FDO driver to access the versions etc. But these drivers normally use the ArcSDE API so you will be forced back to where you started. Good luck. regards Smon On Tue, 03 Jul 2012 10:07:05 +1000, Duarte Carreira (gmail) dncarre...@gmail.com wrote: Hi Olivier. We are an esri shop and have been slowly adopting postgis as our rdbms. We use arcsde direct connections (no arcsde middleware service, just sde's schema with tables, functions, triggrers, etc.). The other way, using native connections from arcmap is just too slow (from the File menu). Also, we obviously want to use postgis native geometry type. But what we found out was that arcmap is 400% slower in some of our use cases. We narrowed it down to the sql emitted by arcmap. ArcMap was taking 38s to draw a spatial table (full zoom), while qgis took 15s. When using esri's st_geometry type arcmap would drop to 8s. Please note this is a single use case, others were also consistently slower but not nearly as much. We opened a support ticket and the first answer was it's just the way postgis works. Which led me to send the sql that I logged when arcmap draw the table and when qgis did it. The difference was in the sql sentence used by each. One query took 38s another took 15s, for the same data. Eventually I was told an enhancement request was opened, but it is not public and not searchable. At this point, we have some scripts that copy data from esri's st_geometry to postgis geometry type, keeping in fact 2 copies of the data: one for esri clients another for open source tools. This is not mandatory, just a choice given our scenario. I think others could have chosen just the postgis geometry type and live with arcmap performing badly with some tables. Another final note: it was not easy for us to copy from esri's st_geometry to postgis geometry using only sql. Since we can only use postgis 1.4 with esri, the wkt produced by esri's st_astext is not compatible with postgis geometryfromtext at 1.4 when there are z or m coords. Neither are binary forms compatible. We ended up using esri's tools and arcsde configuration keywords to make the conversion happen (which is a bit ironic I think). It seems postgis 2.0 would accept esri's st_astext output but i haven't tried it yet. So there you go. Just my 2 cents... all that being said, I enjoy working with arcmap+postgis. The thing that is more confusing to me is handling the server memory usage from arcmap sessions - they can go from 100mb to 1gb. Regards, Duarte -- View this message in context: http://postgis.17.n6.nabble.com/ArcGIS-and-PostGIS-feedback-tp4998679p4998715.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 -- Holder of 2011 Oracle Spatial Excellence Award for Education and Research. SpatialDB Advice and Design, Solutions Architecture and Programming, Oracle Database 10g Administrator Certified Associate; Oracle Database 10g SQL Certified Professional Oracle Spatial, SQL Server, PostGIS, MySQL, ArcSDE, Manifold GIS, FME, Radius Topology and Studio Specialist. 39 Cliff View Drive, Allens Rivulet, 7150, Tasmania, Australia. Website: www.spatialdbadvisor.com Email: si...@spatialdbadvisor.com Voice: +61 362 396397 Mobile: +61 418 396391 Skype: sggreener Longitude: 147.20515 (147° 12' 18 E) Latitude: -43.01530 (43° 00' 55 S) GeoHash: r22em9r98wg NAC:W80CK 7SWP3 ___ postgis-users mailing list
Re: [postgis-users] ArcGIS and PostGIS feedback
On Jul 3, 2012, at 2:07 AM, Duarte Carreira (gmail) wrote: Duarte, [...] So there you go. Just my 2 cents... all that being said, I enjoy working with arcmap+postgis. [...] Thanks a lot for you very detailled feedback, really appreciated ! -- Olivier ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
Re: [postgis-users] pgsql2shp
On 03/07/12 07:14, James Sewell wrote: Hello, When I run pgsql2shp like so: pgsql2shp -u plm25 -g the_geom database table I get the following output: Xcolumn number -1 is out of range 0..1 column number -1 is out of range 0..1 column number -1 is out of range 0..1 column number -1 is out of range 0..1 column number -1 is out of range 0..1 column number -1 is out of range 0..1 column number -1 is out of range 0..1 column number -1 is out of range 0..1 column number -1 is out of range 0..1 column number -1 is out of range 0..1 column number -1 is out of range 0..1 column number -1 is out of range 0..1 This continues in blocks for (I assume) each row in the table. When I go to load the shape I get null values for all rows as follows: INSERT INTO table (the_geom) VALUES (NULL); My versions are: PostgreSQL 9.1.4 on sparc-sun-solaris2.10, compiled by cc: Sun C 5.11 SunOS_sparc 145354-05 2011/09/13, 64-bit POSTGIS=2.0.1 r9979 GEOS=3.3.5-CAPI-1.7.5 PROJ=Rel. 4.8.0, 6 March 2012 GDAL=GDAL 1.9.1, released 2012/05/15 GDAL_DATA not found LIBXML=2.7.8 LIBJSON=UNKNOWN R ASTER Any ideas? This is a fresh table, not migrated form an old version. That's strange - I don't see that error message within the PostGIS codebase at all, so maybe it is coming from libpq. Do you see that error with just that one table, or with a shapefile you have loaded using shp2pgsql? Any chance of sharing your table schema with us, e.g. the output of \d table in psql? ATB, Mark. ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
Re: [postgis-users] Super weird problem: Cannot insert more than ~300 rows (or ~100 kb) into a table
Well, I did some more testing, and found out that the failed query and
subsequent crash of the postgresql client isn't caused by the lat/lng
coordinates... It happens consistently after a certain number of rows (and/or
amount of data) is inserted into the table. Usually close to 300 rows (or 96KB
of data according to psql).
postgis_full_version
--
POSTGIS=1.5.4 GEOS=3.3.2-CAPI-1.7.2 PROJ=Rel. 4.8.0, 6 March 2012
LIBXML=2.7.8 USE_STATS
So, very strange... If I insert programmatically 280-290 rows (varies seemingly
depending on the amount of data in each row), then try to add rows one at a
time, here's what happens:
mydb=# select count(*) from addresses;INSERT INTO addresses ( account_id,
territory_id, location ) VALUES ( 1, 75, ST_GeomFromText('POINT(-114.267388
51.089941)') );
count
---
345
(1 row)
INSERT 0 1
mydb=# select count(*) from addresses;INSERT INTO addresses ( account_id,
territory_id, location ) VALUES ( 1, 75, ST_GeomFromText('POINT(-114.267388
51.089941)') );
count
---
346
(1 row)
The connection to the server was lost. Attempting reset: Failed.
! select count(*) from addresses;INSERT INTO addresses ( account_id,
territory_id, location ) VALUES ( 1, 75, ST_GeomFromText('POINT(-114.267388
51.089941)') );
You are currently not connected to a database.
If I quit and restart psql, same error occurs. Only if I drop the table can I
insert more rows — but again, only up to around 300 rows or 100 kb (not sure
where the limit is)... So, it's not dependent on the kind of coordinates or row
data, just the number of inserts and/or the amount of data. Any ideas what is
causing the problem here?
On 2012-07-02, at 1:22 PM, Sandro Santilli wrote:
Please file a ticket, and report the output of postgis_full_version()
--strk;
On Mon, Jul 02, 2012 at 01:10:55PM +0200, René Fournier wrote:
If I try to insert a row containing particular coordinate, the query fails
and the DB connection is lost. (By comparison, hundreds of inserts of other
coordinates work fine.) Here's a straight copy-and-paste comparison from
psql:
mydb=# INSERT INTO addresses ( account_id, territory_id, location ) VALUES (
1, 0, ST_GeomFromText('POINT(-114.112534 50.895364)') ) RETURNING id;
id
-
333
(1 row)
INSERT 0 1
mydb=# INSERT INTO addresses ( account_id, territory_id, location ) VALUES (
1, 0, ST_GeomFromText('POINT(-114.228869 51.152249)') ) RETURNING id;
The connection to the server was lost. Attempting reset: Failed.
!
Here's the table definition:
CREATE TABLE public.addresses
(id serial NOT NULL,
account_id int NOT NULL,
territory_id int NOT NULL,
location GEOGRAPHY(POINT,4326),
PRIMARY KEY (id));
CREATE INDEX location ON addresses USING GIST (location);
Strange right? FWIW, the queries are being generated programmatically by a
script, so the error is not caused by a typo, since hundreds of other
inserts work. Also, I've done a little research, two interesting findings:
1. All the multiplied coordinate values (abs(lat)*abs(lng)) of the
SUCCESSFUL inserts are LOWER than the coordinates of failed query.
2. If I create the table without the index on location, the failed inserts
suddenly work. So it seems the problem lies with the PostGIS updating the
Index -- maybe it doesn't like the size of the values of the larger
coordinates?
Anyway, if you have any ideas of what I can do to fix this, I would love to
hear them. Thanks!
...Rene
___
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] intersection problem
hi,
I have a problem with an intersection with 2 lines that don't return
what I expect:
sample intersection between a line and a substring of the same line:
first line: LINESTRING(675315.7 158956.9,675280 158912.9)
second: st_makeline(geomfromtext('POINT(675315.7 158956.9)'),
ST_Line_Interpolate_Point(geomfromtext('LINESTRING(675315.7
158956.9,675280 158912.9)'),0.6))
select st_astext(st_intersection(
geomfromtext('LINESTRING(675315.7 158956.9,675280 158912.9)'),
st_makeline(geomfromtext('POINT(675315.7 158956.9)'),
ST_Line_Interpolate_Point(geomfromtext('LINESTRING(675315.7
158956.9,675280 158912.9)'),0.6))
));
result: POINT(675315.7 158956.9)
what I expect is that the result of the intersection is not only the
point of start of the 2 lines, but a linestring
starting with that point but ending with the point returned by the
interpolation (expected to be over the line)
I'm doing something wrong with the intersection? there is another way to
get the result I expect?
or is a problem in postgis?
ps: I'm using the POSTGIS 1.5.3 version
thx for help
Daniele
___
postgis-users mailing list
postgis-users@postgis.refractions.net
http://postgis.refractions.net/mailman/listinfo/postgis-users
[postgis-users] Length of a line above polygon
Hi all, We have a layer that contains polygons and another that contains lines that lie on them. Parts of the lines are laying inside the polygons and I would like to get a sum of all the lengths of lines that lie on the polygons. What is the easiest way to achieve this? TIA, Matej ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
Re: [postgis-users] Length of a line above polygon
Hi, You can use something like SELECT areas.id, SUM( ST_Length( lines.geometry) ) FROM lines,areas WHERE ST_Intersects(lines.geometry,areas.geometry) GROUP BY areas.id ; Greetings, Denis On 07/03/2012 01:36 PM, Matej Mailing wrote: Hi all, We have a layer that contains polygons and another that contains lines that lie on them. Parts of the lines are laying inside the polygons and I would like to get a sum of all the lengths of lines that lie on the polygons. What is the easiest way to achieve this? TIA, Matej ___ 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] Super weird problem: Cannot insert more than ~300 rows (or ~100 kb) into a table
On 03/07/12 12:34, René Fournier wrote:
Well, I did some more testing, and found out that the failed query and
subsequent crash of the postgresql client isn't caused by the lat/lng
coordinates... It happens consistently after a certain number of rows
(and/or amount of data) is inserted into the table. Usually close to 300
rows (or 96KB of data according to psql).
postgis_full_version
--
POSTGIS=1.5.4 GEOS=3.3.2-CAPI-1.7.2 PROJ=Rel. 4.8.0, 6 March 2012
LIBXML=2.7.8 USE_STATS
So, very strange... If I insert programmatically 280-290 rows (varies
seemingly depending on the amount of data in each row), then try to add
rows one at a time, here's what happens:
mydb=# select count(*) from addresses;INSERT INTO addresses (
account_id, territory_id, location ) VALUES ( 1, 75,
ST_GeomFromText('POINT(-114.267388 51.089941)') );
count
---
345
(1 row)
INSERT 0 1
mydb=# select count(*) from addresses;INSERT INTO addresses (
account_id, territory_id, location ) VALUES ( 1, 75,
ST_GeomFromText('POINT(-114.267388 51.089941)') );
count
---
346
(1 row)
The connection to the server was lost. Attempting reset: Failed.
! select count(*) from addresses;INSERT INTO addresses (
account_id, territory_id, location ) VALUES ( 1, 75,
ST_GeomFromText('POINT(-114.267388 51.089941)') );
You are currently not connected to a database.
If I quit and restart psql, same error occurs. Only if I drop the table
can I insert more rows — but again, only up to around 300 rows or 100 kb
(not sure where the limit is)... So, it's not dependent on the kind of
coordinates or row data, just the number of inserts and/or the amount of
data. Any ideas what is causing the problem here?
Strange - from what you're saying, it sounds as if it could be to do
with the index splitting code, but without a backtrace
(http://trac.osgeo.org/postgis/wiki/DevWikiGettingABackTrace) it's
tricky to say exactly.
One small nit - I notice you've got a geography column but you're using
ST_GeomFromText() rather than ST_GeogFromText() which is likely
introducing an extra cast somewhere. Does changing to ST_GeogFromText()
help prevent the crash at all?
ATB,
Mark.
___
postgis-users mailing list
postgis-users@postgis.refractions.net
http://postgis.refractions.net/mailman/listinfo/postgis-users
Re: [postgis-users] Super weird problem: Cannot insert more than ~300 rows (or ~100 kb) into a table
On 2012-07-03, at 1:43 PM, Mark Cave-Ayland wrote: On 03/07/12 12:34, René Fournier wrote: If I quit and restart psql, same error occurs. Only if I drop the table can I insert more rows — but again, only up to around 300 rows or 100 kb (not sure where the limit is)... So, it's not dependent on the kind of coordinates or row data, just the number of inserts and/or the amount of data. Any ideas what is causing the problem here? Strange - from what you're saying, it sounds as if it could be to do with the index splitting code, but without a backtrace (http://trac.osgeo.org/postgis/wiki/DevWikiGettingABackTrace) it's tricky to say exactly. One small nit - I notice you've got a geography column but you're using ST_GeomFromText() rather than ST_GeogFromText() which is likely introducing an extra cast somewhere. Does changing to ST_GeogFromText() help prevent the crash at all? Hi Mark, Thanks for the tip... I changed to GeogFromText(), but the error/crash still occurs, at the same point... After the crash, here's what the tables look like: List of relations Schema |Name| Type | Owner |Size| Description ++--+--++- public | accounts | table| postgres | 8192 bytes | public | accounts_id_seq| sequence | postgres | 8192 bytes | public | addresses | table| postgres | 96 kB | public | addresses_id_seq | sequence | postgres | 8192 bytes | public | geography_columns | view | postgres | 0 bytes| public | geometry_columns | table| postgres | 8192 bytes | public | languages | table| postgres | 8192 bytes | public | languages_id_seq | sequence | postgres | 8192 bytes | public | spatial_ref_sys| table| postgres | 3000 kB| public | territories| table| postgres | 416 kB | public | territories_id_seq | sequence | postgres | 8192 bytes | public | users | table| postgres | 96 kB | public | users_id_seq | sequence | postgres | 8192 bytes | Is it strange that the geography_columns view is 0 bytes? Even though both territories and addresses have a geography column (POLYGON and POINT respectively) and an associated GIST index? ...Rene ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
Re: [postgis-users] Super weird problem: Cannot insert more than ~300 rows (or ~100 kb) into a table
So, it seems that the table gets full and corrupted to some extent. After my
import script inserts ~290 rows, and then postgres crashes...
mydb=# select count(*) from addresses;INSERT INTO addresses ( account_id,
territory_id, location ) VALUES ( 1, 75, ST_GeomFromText('POINT(-114.267388
51.089941)') );
count
---
284
(1 row)
The connection to the server was lost. Attempting reset: Failed.
So, can't insert any more rows...
! delete from addresses where id 50;
You are currently not connected to a database.
Postgres client/connection is crashed.
! \q
Tue Jul 03 14:15:28 -- rene /opt/local/var/db:: psql -U postgres mydb
psql (9.1.4)
Type help for help.
mydb=# delete from addresses where id 50;
DELETE 234
Deleting rows works...
mydb=# select count(*) from addresses;INSERT INTO addresses ( account_id,
territory_id, location ) VALUES ( 1, 75, ST_GeomFromText('POINT(-114.267388
51.089941)') );
count
---
50
(1 row)
The connection to the server was lost. Attempting reset: Failed.
SELECT and DELETE work, but I can't insert any new rows, until...
! \q
Tue Jul 03 14:15:39 -- rene /opt/local/var/db:: psql -U postgres mydb
psql (9.1.4)
Type help for help.
mydb=# vacuum;vacuum full;vacuum full analyze;
VACUUM
VACUUM
NOTICE: no notnull values, invalid stats
VACUUM
mydb=# select count(*) from addresses;INSERT INTO addresses ( account_id,
territory_id, location ) VALUES ( 1, 75, ST_GeomFromText('POINT(-114.267388
51.089941)') );
count
---
50
(1 row)
INSERT 0 1
mydb=#
So, it appears there's some weird corruption going on... Still, not sure what
to try next. My PostGIS is via Macports, not sure how to enable the debug
mode...
On 2012-07-03, at 2:03 PM, René Fournier wrote:
On 2012-07-03, at 1:43 PM, Mark Cave-Ayland wrote:
On 03/07/12 12:34, René Fournier wrote:
If I quit and restart psql, same error occurs. Only if I drop the table
can I insert more rows — but again, only up to around 300 rows or 100 kb
(not sure where the limit is)... So, it's not dependent on the kind of
coordinates or row data, just the number of inserts and/or the amount of
data. Any ideas what is causing the problem here?
Strange - from what you're saying, it sounds as if it could be to do with
the index splitting code, but without a backtrace
(http://trac.osgeo.org/postgis/wiki/DevWikiGettingABackTrace) it's tricky to
say exactly.
One small nit - I notice you've got a geography column but you're using
ST_GeomFromText() rather than ST_GeogFromText() which is likely introducing
an extra cast somewhere. Does changing to ST_GeogFromText() help prevent the
crash at all?
Hi Mark,
Thanks for the tip... I changed to GeogFromText(), but the error/crash still
occurs, at the same point... After the crash, here's what the tables look
like:
List of relations
Schema |Name| Type | Owner |Size| Description
++--+--++-
public | accounts | table| postgres | 8192 bytes |
public | accounts_id_seq| sequence | postgres | 8192 bytes |
public | addresses | table| postgres | 96 kB |
public | addresses_id_seq | sequence | postgres | 8192 bytes |
public | geography_columns | view | postgres | 0 bytes|
public | geometry_columns | table| postgres | 8192 bytes |
public | languages | table| postgres | 8192 bytes |
public | languages_id_seq | sequence | postgres | 8192 bytes |
public | spatial_ref_sys| table| postgres | 3000 kB|
public | territories| table| postgres | 416 kB |
public | territories_id_seq | sequence | postgres | 8192 bytes |
public | users | table| postgres | 96 kB |
public | users_id_seq | sequence | postgres | 8192 bytes |
Is it strange that the geography_columns view is 0 bytes? Even though both
territories and addresses have a geography column (POLYGON and POINT
respectively) and an associated GIST index?
...Rene
___
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] Problem with using query layer in ArcGIS 10
Hi William, Thank you for response. It is great that you showed interest in helping me on this. After playing with the query tool, it was very evident that I need to pick one single unique identifier field. The problem I am facing happens despite the fact that I selected one single field as unique identifier. I have two layers (one with 13,000 records and another with 55,600 records). Smaller file works like any other shape file. The larger file would through up loading data problem. Note: If I, either select a bunch features or use identify tool, I could see all the attributes. In other cases, though I could not see attributes, I could see all the features on the map. I could also use attribute query tool on the same data and select records and view them. However, I simply having trouble with opening the table and viewing all the records. Also I am unable to export to shapefile from ArcMap when this problem occurs. Thanks. Message: 5 Date: Mon, 02 Jul 2012 13:41:33 +0200 From: William Humphrey Temperley william.temper...@jrc.ec.europa.eumailto:william.temper...@jrc.ec.europa.eu Subject: Re: [postgis-users] Problem with using query layer in ArcGIS 10 To: 'PostGIS Users Discussion' postgis-users@postgis.refractions.netmailto:postgis-users@postgis.refractions.net Message-ID: 032201cd5847$a1790e40$e46b2ac0$@jrc.ec.europa.eumailto:032201cd5847$a1790e40$e46b2ac0$@jrc.ec.europa.eu Content-Type: text/plain; charset=us-ascii From: postgis-users-boun...@postgis.refractions.netmailto:postgis-users-boun...@postgis.refractions.net [mailto:postgis-users-boun...@postgis.refractions.net] On Behalf Of Melpati, Muni Sent: 28 June 2012 21:19 To: postgis-users@postgis.refractions.netmailto:postgis-users@postgis.refractions.net Subject: [postgis-users] Problem with using query layer in ArcGIS 10 Hi I was querying posgis layers from ArcGIS 10. I found that if add postgis layer with huge number of records, I am having trouble with accessing its attributes. When I try to open the table I get Could not load data from the data source. If you can correct the problem, press the refresh button to reload data. Possible problems can include bad network connection, invalid field, etc. The SQL statement was not a select statement. The operation is not supported by this implementation. I found similar problem somewhere in GIS forums where the user had to change the MAXBLOBSIZE value to -1 (unlimited) in the SDE SERVER CONFIG FILE. Is there similar fix to postgis/postgres? I appreciate your support. Thanks. By default ArcGIS uses all the fields in the query as the unique key. Tools such as identify labeling and the attribute table do not work when this is the case. To make them work the primary key of the data source should be used as the unique key. This also speeds up the loading of data. After writing a query, you need to click the Validate button and tick Show advanced options. The Next button will now be highlighted - click it. Uncheck all fields in the Unique identifier fields list except the correct primary key or unique field. The attribute table should now work if you provided a unique key. HTH, Will Temperley ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
Re: [postgis-users] Problem with using query layer in ArcGIS 10
I haven't seen this behaviour -- being able to see the attributes with identify but not being able to use the attribute table properly. Are you 100% certain the unique identifier column you have chosen is unique, i.e. is it a primary key, or does it have a unique constraint on it? Duplicate keys might cause this behaviour, but I'm not sure. Will From: postgis-users-boun...@postgis.refractions.net [mailto:postgis-users-boun...@postgis.refractions.net] On Behalf Of Melpati, Muni Sent: 03 July 2012 17:28 To: postgis-users@postgis.refractions.net Subject: [postgis-users] Problem with using query layer in ArcGIS 10 Hi William, Thank you for response. It is great that you showed interest in helping me on this. After playing with the query tool, it was very evident that I need to pick one single unique identifier field. The problem I am facing happens despite the fact that I selected one single field as unique identifier. I have two layers (one with 13,000 records and another with 55,600 records). Smaller file works like any other shape file. The larger file would through up loading data problem. Note: If I, either select a bunch features or use identify tool, I could see all the attributes. In other cases, though I could not see attributes, I could see all the features on the map. I could also use attribute query tool on the same data and select records and view them. However, I simply having trouble with opening the table and viewing all the records. Also I am unable to export to shapefile from ArcMap when this problem occurs. Thanks. Message: 5 Date: Mon, 02 Jul 2012 13:41:33 +0200 From: William Humphrey Temperley william.temper...@jrc.ec.europa.eu Subject: Re: [postgis-users] Problem with using query layer in ArcGIS 10 To: 'PostGIS Users Discussion' postgis-users@postgis.refractions.net Message-ID: 032201cd5847$a1790e40$e46b2ac0$@jrc.ec.europa.eu Content-Type: text/plain; charset=us-ascii From: postgis-users-boun...@postgis.refractions.net [mailto:postgis-users-boun...@postgis.refractions.net] On Behalf Of Melpati, Muni Sent: 28 June 2012 21:19 To: postgis-users@postgis.refractions.net Subject: [postgis-users] Problem with using query layer in ArcGIS 10 Hi I was querying posgis layers from ArcGIS 10. I found that if add postgis layer with huge number of records, I am having trouble with accessing its attributes. When I try to open the table I get Could not load data from the data source. If you can correct the problem, press the refresh button to reload data. Possible problems can include bad network connection, invalid field, etc. The SQL statement was not a select statement. The operation is not supported by this implementation. I found similar problem somewhere in GIS forums where the user had to change the MAXBLOBSIZE value to -1 (unlimited) in the SDE SERVER CONFIG FILE. Is there similar fix to postgis/postgres? I appreciate your support. Thanks. By default ArcGIS uses all the fields in the query as the unique key. Tools such as identify labeling and the attribute table do not work when this is the case. To make them work the primary key of the data source should be used as the unique key. This also speeds up the loading of data. After writing a query, you need to click the Validate button and tick Show advanced options. The Next button will now be highlighted - click it. Uncheck all fields in the Unique identifier fields list except the correct primary key or unique field. The attribute table should now work if you provided a unique key. HTH, Will Temperley ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
Re: [postgis-users] Super weird problem: Cannot insert more than ~300 rows (or ~100 kb) into a table
On 03/07/12 13:20, René Fournier wrote:
So, it seems that the table gets full and corrupted to some extent.
After my import script inserts ~290 rows, and then postgres crashes...
mydb=# select count(*) from addresses;INSERT INTO addresses (
account_id, territory_id, location ) VALUES ( 1, 75,
ST_GeomFromText('POINT(-114.267388 51.089941)') );
count
---
284
(1 row)
The connection to the server was lost. Attempting reset: Failed.
So, can't insert any more rows...
! delete from addresses where id 50;
You are currently not connected to a database.
Postgres client/connection is crashed.
! \q
Tue Jul 03 14:15:28 -- rene /opt/local/var/db:: psql -U postgres
mydb psql (9.1.4)
Type help for help.
mydb=# delete from addresses where id 50;
DELETE 234
Deleting rows works...
mydb=# select count(*) from addresses;INSERT INTO addresses (
account_id, territory_id, location ) VALUES ( 1, 75,
ST_GeomFromText('POINT(-114.267388 51.089941)') );
count
---
50
(1 row)
The connection to the server was lost. Attempting reset: Failed.
SELECT and DELETE work, but I can't insert any new rows, until...
! \q
Tue Jul 03 14:15:39 -- rene /opt/local/var/db:: psql -U postgres
mydb psql (9.1.4)
Type help for help.
mydb=# vacuum;vacuum full;vacuum full analyze;
VACUUM
VACUUM
NOTICE: no notnull values, invalid stats
VACUUM
mydb=# select count(*) from addresses;INSERT INTO addresses (
account_id, territory_id, location ) VALUES ( 1, 75,
ST_GeomFromText('POINT(-114.267388 51.089941)') );
count
---
50
(1 row)
INSERT 0 1
mydb=#
So, it appears there's some weird corruption going on... Still, not sure
what to try next. My PostGIS is via Macports, not sure how to enable the
debug mode...
Hi René,
I think that you need to create a new bug on the PostGIS bug tracker and
upload a file that causes the crash on your system, i.e. it can be run
using psql -d postgis_db -f crash.sql so that we can try and reproduce
what you are seeing.
Also as a matter of interest, do you see anything interesting in the
PostgreSQL log file at the time of the crash?
ATB,
Mark.
___
postgis-users mailing list
postgis-users@postgis.refractions.net
http://postgis.refractions.net/mailman/listinfo/postgis-users
Re: [postgis-users] Extracting cell values into a matrix
I need to do an ordered weighted averaging with the input rasters: assuming they all have the same extension and characteristics (like pixel size), for position (1,1) I take the value of every raster, the I sort those values to assign the weights and finally I compute the average to store it in the (1,1) position of the output raster. And so on with the rest of the positions. How many overlapping raster do you have? How do you determine the right weight? ST_Union can compute the mean of many overlapping rasters. Maybe a simple modification of the code (it is written in plpgsql) would do what you want. If there are not so many overlaping raster ST_MapAlgebra(raster, raster) could also be used depending on how you determine the right weight. Pierre ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
Re: [postgis-users] Super weird problem: ST_GeomFromText('POINT(-114.112534 50.895364)') works, ST_GeomFromText('POINT(-114.228869 51.152249)') fails -- why?
Yes, it's very important to know your version/platform.
Also, does this point crash the back-end when inserted into an empty
table, or only when inserted after the initial series of points. If
the latter, then the problem isn't the particular point, but how the
index is dealing with your particular *collection* of points, so it
will be useful to know what that collection is.
P.
On Mon, Jul 2, 2012 at 4:10 AM, René Fournier m...@renefournier.com wrote:
If I try to insert a row containing particular coordinate, the query fails
and the DB connection is lost. (By comparison, hundreds of inserts of other
coordinates work fine.) Here's a straight copy-and-paste comparison from
psql:
mydb=# INSERT INTO addresses ( account_id, territory_id, location ) VALUES (
1, 0, ST_GeomFromText('POINT(-114.112534 50.895364)') ) RETURNING id;
id
-
333
(1 row)
INSERT 0 1
mydb=# INSERT INTO addresses ( account_id, territory_id, location ) VALUES (
1, 0, ST_GeomFromText('POINT(-114.228869 51.152249)') ) RETURNING id;
The connection to the server was lost. Attempting reset: Failed.
!
Here's the table definition:
CREATE TABLE public.addresses
(id serial NOT NULL,
account_id int NOT NULL,
territory_id int NOT NULL,
location GEOGRAPHY(POINT,4326),
PRIMARY KEY (id));
CREATE INDEX location ON addresses USING GIST (location);
Strange right? FWIW, the queries are being generated programmatically by a
script, so the error is not caused by a typo, since hundreds of other
inserts work. Also, I've done a little research, two interesting findings:
1. All the multiplied coordinate values (abs(lat)*abs(lng)) of the
SUCCESSFUL inserts are LOWER than the coordinates of failed query.
2. If I create the table without the index on location, the failed inserts
suddenly work. So it seems the problem lies with the PostGIS updating the
Index -- maybe it doesn't like the size of the values of the larger
coordinates?
Anyway, if you have any ideas of what I can do to fix this, I would love to
hear them. Thanks!
...Rene
___
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] Problem with using query layer in ArcGIS
The selected unique identifier column is GID (created by postgis). So, it has to be unique identifier. Based on the fact that I could see selected feature attributes or I could see attributes using identifier tool, I have to assume that query tool retrieves this information upon user action (in this case select a feature). That is why I am wondering if I need to modify something like it was done with SDE Server config file to improve the performance of the query application. Thanks. Message: 18 Date: Tue, 03 Jul 2012 17:50:55 +0200 From: William Humphrey Temperley william.temper...@jrc.ec.europa.eumailto:william.temper...@jrc.ec.europa.eu Subject: Re: [postgis-users] Problem with using query layer in ArcGIS 10 To: 'PostGIS Users Discussion' postgis-users@postgis.refractions.netmailto:postgis-users@postgis.refractions.net Message-ID: 013c01cd5933$a1af3230$e50d9690$@jrc.ec.europa.eumailto:013c01cd5933$a1af3230$e50d9690$@jrc.ec.europa.eu Content-Type: text/plain; charset=us-ascii I haven't seen this behaviour -- being able to see the attributes with identify but not being able to use the attribute table properly. Are you 100% certain the unique identifier column you have chosen is unique, i.e. is it a primary key, or does it have a unique constraint on it? Duplicate keys might cause this behaviour, but I'm not sure. Will From: postgis-users-boun...@postgis.refractions.netmailto:postgis-users-boun...@postgis.refractions.net [mailto:postgis-users-boun...@postgis.refractions.net] On Behalf Of Melpati, Muni Sent: 03 July 2012 17:28 To: postgis-users@postgis.refractions.netmailto:postgis-users@postgis.refractions.net Subject: [postgis-users] Problem with using query layer in ArcGIS 10 Hi William, Thank you for response. It is great that you showed interest in helping me on this. After playing with the query tool, it was very evident that I need to pick one single unique identifier field. The problem I am facing happens despite the fact that I selected one single field as unique identifier. I have two layers (one with 13,000 records and another with 55,600 records). Smaller file works like any other shape file. The larger file would through up loading data problem. Note: If I, either select a bunch features or use identify tool, I could see all the attributes. In other cases, though I could not see attributes, I could see all the features on the map. I could also use attribute query tool on the same data and select records and view them. However, I simply having trouble with opening the table and viewing all the records. Also I am unable to export to shapefile from ArcMap when this problem occurs. Thanks. ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
[postgis-users] Compilation problem for Postgis-1.5.4 on Debian Squeeze
Hello, Ive some problems to make Postgis-1.5.4 compilation On Debian Squeeze. After the ./configure , make command end like that: gcc -g -O2 -g -Wall -O2 -fPIC -Wall -Wmissing-prototypes -Wpointer-arith -Wdeclaration-after-statement -Wendif-labels -fno-strict-aliasing -fwrapv -g -fpic -I/usr/include -I/usr/include/libxml2 -I../liblwgeom -I. -I. -I/usr/include/postgresql/9.0/server -I/usr/include/postgresql/internal -D_GNU_SOURCE -I/usr/include/libxml2 -I/usr/include/tcl8.5 -c -o lwgeom_cache.o lwgeom_cache.c In file included from lwgeom_cache.h:21, from lwgeom_cache.c:16: lwgeom_geos_prepared.h:60: erreur: expected :, ,, ;, } or __attribute__ before * token make[1]: *** [lwgeom_cache.o] Erreur 1 make[1]: quittant le répertoire « /source/postgis-1.5.4/postgis » make: *** [postgis] Erreur 2 gcc (Debian 4.4.5-8) 4.4.5 on Debian Squeeze The ./configure result: PostGIS is now configured for i686-pc-linux-gnu -- Compiler Info - C compiler: gcc -g -O2 C++ compiler: g++ -g -O2 -- Dependencies -- GEOS config: /usr/bin/geos-config GEOS version: 3.2.0 PostgreSQL config:/usr/bin/pg_config PostgreSQL version: PostgreSQL 9.0.7 PROJ4 version:47 Libxml2 config: /usr/bin/xml2-config Libxml2 version: 2.7.8 PostGIS debug level: 0 Documentation Generation xsltproc: /usr/bin/xsltproc xsl style sheets: dblatex: /usr/bin/dblatex convert: /usr/bin/convert Thanks for any idea Eric ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
Re: [postgis-users] Problem with using query layer in ArcGIS
Can you interact with the postgresql/Postgis data natively (ie via pgAminIII) without error? Can't think of any case where this might not be the case. This smells very much like an ArcGIS/ArcSDE configuration problem. In the old days, when one used the ArcSDE administration command line tools, one would use sdelayer to create a layer over an existing table. That sdelayer command included arguments to tell ArcSDE what column contained the unique identifier and whether it was SDE maintained or user maintained. ArcSDE, back then, didn't require the column to be a primary key, only unique.sdelayer -o add -l table,column -e entity_mask [-t storage_type] [Spatial_Index][{-R SRID | [Spatial_Ref_Opts]}] [-M minimum_id] [{-f init_features,avg_points | -k config_keyword}] [-E {empty | xmin,ymin,xmax,ymax}] [-L {ON | OFF}] [-C row_id_column[,{SDE|USER}[,min_ID]]] [-P {HIGH | BASIC}] [-S layer_description_str] [-i {service | port# | direct connection}] [-s server_name] [-D database_name] -u DB_user_name -p DB_user_password [-q]For -C .Specifies the name of the row ID column of the layer's business table and the optional minimum row ID valueFor the register operation, it also specifies whether the row ID is to be registered as user or ArcSDE maintained. If ArcSDE maintained, you can specify the starting value (min_ID) for the row ID. If no minimum ID is specified, it defaults to 1. If you intend to register the feature class with the geodatabase, you should register the row ID column as ArcSDE maintained.For the feature_info operation, it specifies the name of the row ID column that will be output to a table in the database.Double check that this is your table/column configuration aligns with what I have said.regardsSimonOn Wed, 04 Jul 2012 06:00:23 +1000, Melpati, Muni muni.melp...@dot.state.fl.us wrote:The selected unique identifier column is GID (created by postgis). So, it has to be unique identifier.Based on the fact that I could see selected feature attributes or I could see attributes using identifier tool,I have to assume that query tool retrieves this information upon user action (in this case select a feature).That is why I am wondering if I need to modify something like it was done with SDE Server config file to improve the performance of the query application. Thanks.Message: 18Date: Tue, 03 Jul 2012 17:50:55 +0200From: William Humphrey Temperley william.temper...@jrc.ec.europa.euSubject: Re: [postgis-users] Problem with using query layer in ArcGIS 10To: "'PostGIS Users Discussion'" postgis-users@postgis.refractions.netMessage-ID: 013c01cd5933$a1af3230$e50d9690$@jrc.ec.europa.euContent-Type: text/plain; charset="us-ascii"I haven't seen this behaviour -- being able to see the attributes withidentify but not being able to use the attribute table properly. Are you 100% certain the unique identifier column you have chosen is unique,i.e. is it a primary key, or does it have a unique constraint on it?Duplicate keys might cause this behaviour, but I'm not sure. Will From: postgis-users-boun...@postgis.refractions.net[mailto:postgis-users-boun...@postgis.refractions.net] On Behalf Of Melpati,MuniSent: 03 July 2012 17:28To: postgis-users@postgis.refractions.netSubject: [postgis-users] Problem with using query layer in ArcGIS 10 Hi William,Thank you for response. It is great that you showed interest in helping meon this. After playing with the query tool, it was very evident that I needto pick one single unique identifier field. The problem I am facing happensdespite the fact that I selected one single field as unique identifier. Ihave two layers (one with 13,000 records and another with 55,600 records).Smaller file works like any other shape file. The larger file would throughup loading data problem. Note: If I, either select a bunch features or use identify tool, I couldsee all the attributes. In other cases, though I could not see attributes, Icould see all the features on the map.I could also use attribute query tool on the same data and select recordsand view them. However, I simply having trouble with opening the table andviewing all the records. Also I am unable to export to shapefile from ArcMap when this problemoccurs.Thanks.-- Holder of "2011 Oracle Spatial Excellence Award for Education and Research."SpatialDB Advice and Design, Solutions Architecture and Programming,Oracle Database 10g Administrator Certified Associate; Oracle Database 10g SQL Certified ProfessionalOracle Spatial, SQL Server, PostGIS, MySQL, ArcSDE, Manifold GIS, FME, Radius Topology and Studio Specialist.39 Cliff View Drive, Allens Rivulet, 7150, Tasmania, Australia.Website: www.spatialdbadvisor.com Email: si...@spatialdbadvisor.com Voice: +61 362 396397Mobile: +61 418 396391Skype: sggreenerLongitude: 147.20515 (147° 12' 18" E)Latitude: -43.01530 (43° 00' 55" S)GeoHash: r22em9r98wgNAC:W80CK 7SWP3___
postgis-users mailing list
postgis-users@postgis.refractions.net
