[postgis-users] tweaking a query to use less memory
Hello, I am running postGIS on a Ubuntu linux laptop with 4Gb of memory. I need to calculate a value for each of 320,000 polygons based on neighboring polygons (within 1/10 of a mile). I stopped the query below after 2.5 hours because it used 3.8Gb of memory and the swap was at 1Gb and rising. Can anyone recommend changes in the query that would allow it to run in RAM? Do I need a more powerful machine (or more patience) for tasks like this? thank you, -david CREATE TABLE bad_housing_neighbors AS SELECT p1.ogc_fid, avg(case when p2.vod='1' or p2.fire='1' or p2.condition='3' or p2.condition='4' then 1 else 0 end) --528 feet is 1/10 of a mile FROM parcels09_d3_v2 as p1, parcels09_d3_v2 as p2 WHERE st_intersects(buffer(p1.wkb_geometry,528), p2.wkb_geometry) GROUP BY p1.ogc_fid ORDER BY p1.ogc_fid ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
[postgis-users] side location conflict GEOS touches() threw an error
Hello, I have a postGIS polygon layer with about 320,000 rows. I am trying to return a table with object_number and the average area of the surrounding (touching) polygons. I am getting this error: NOTICE: TopologyException: side location conflict 1.34593e+07 289017 ERROR: GEOS touches() threw an error! ** Error ** ERROR: GEOS touches() threw an error! SQL state: XX000 I have tried to simplify the geometry many different times. Nothing seems to solve the problem. I can process the first 20,000 rows (object_number < 2) but something is causing problems after that. I have pasted the script below, any suggestions would be welcomed! Thank you, -david SELECT p1.ogc_fid, avg(p2.shape_area) FROM parcels09_d3_v2_simplify as p1, parcels09_d3_v2_simplify as p2 WHERE st_touches(p1.wkb_geometry, p2.wkb_geometry) GROUP BY p1.ogc_fid ORDER BY p1.ogc_fid ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
Re: [postgis-users] speeding up "not in" query
Thank you all for your suggestions. My original query not only took 4.6 hours to run, but also returned the wrong answer. The "not exists" version below returned the correct answer (as far as I can tell) in 2 minutes. I thought these were essentially the same query but apparently they are not. I have not yet tried the left join version. SELECT * FROM parcels_esri WHERE NOT EXISTS (SELECT survey.parcelnum FROM survey WHERE parcels_esri.parcelnumb = survey.parcelnum) "Seq Scan on parcels_esri (cost=0.00..3320785.37 rows=193444 width=4537) (actual time=17.793..4154.274 rows=84489 loops=1)" " Filter: (NOT (subplan))" " SubPlan" "-> Index Scan using parcelnum_idx on survey (cost=0.00..8.35 rows=1 width=26) (actual time=0.009..0.009 rows=1 loops=386887)" " Index Cond: ($0 = parcelnum)" "Total runtime: 4200.203 ms" Running EXPLAIN ANALYZE on my original query takes more time than I am willing to wait. This seems like a very problematic formulation: SELECT * FROM parcels_esri WHERE parcels_esri.parcelnumb NOT IN (SELECT survey.parcelnum FROM survey) Thanks again for your help. I now know the huge improvements that can come from such variations. -david ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
[postgis-users] speeding up "not in" query
I have one spatial table and one non-spatial table each with about 380k rows. They can be joined by "parcelnum". However, there are some parcel numbers in each table that do not match. I want the full rows of the non-matching from each. I've made a standard (b-tree) index for "parcelnum" in both tables and then ran this query to get non-matching rows in a single direction: SELECT * FROM survey WHERE survey.parcelnum NOT IN (SELECT parcel_esri.parcelnum FROM parcels_esri) This query has already run for 35 minutes and is still running. I have a laptop running Ubuntu 9.04 64bit with two P8700 2.53GHz CPUs and 3.8GB of memory. Only 1 CPU and 1.3GB of memory is being used. Is there anything I can do to speed up not-in (and join) queries? thank you, -david ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
[postgis-users] st_union with few overlapping elements
Hello, I am new to postGIS and am using the st_union function for the first time. I have a table with 10K polygons and another table with about 100 polygons. The second layer contains elements missing from the first, but there may be some overlap along the edges where they fit together. They have different attribute columns. I want to create a new polygon table with ALL of this data and with the unique key necessary for loading in QGIS. I've seen some relevant examples with intersection, but have not been able to change them to do what I want yet. Pasted below is the code that I tried, which produced a file with 700k rows!!! I could not load it in QGIS so I am not sure what it represents, perhaps all the points? Do I need to pre-define the table that receives the attributes (I hope not, because there are many!) CREATE TABLE all_plots AS SELECT ST_UNION(h.the_geom, m.the_geom) AS union_geom FROM have_plots AS h, missing_plots AS m; Any help would be appreciated! thank you, -david PS: I am currently executing queries in PGADMIN3 to make mass manipulations to the data & using QGIS to visualize, geo-reference, and move specific vertices. Does anyone highly recommend a different combination of tools? ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
Re: [postgis-users] geometry column permission denied
Paul, It has taken some time for me to get back to this problem. You were indeed correct. Now that it is actually working I have some other questions that I will post under another subject. Thank you again for your help! -david Date: Wed, 09 Jan 2008 09:13:35 -0800 From: Paul Ramsey <[EMAIL PROTECTED]> Subject: Re: [postgis-users] geometry column permission denied using shp2pgsql Permission problems and encoding problems are quite separate, so let's examine the first problem first. Your first error is the permission problem. What happens when you log in as yourself with psql and try to insert a column into geometry_columns? Hopefully it should fail, since that's all the shp2pgsql-generated script is doing. Run \d and see who owns that table. Probably it's owned by postgres and you don't have rights to it. Log in as postgres and do some GRANTS to provide yourself permissions and run from there. The encoding issue is separate, and you seem to be on the right track with the -W switch, assuming that ISO-8859-8 is in fact the original encoding. ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
[postgis-users] geometry column permission denied using shp2pgsql
Hello,
I am the owner of the database but am getting a permission
denied on the
geometry column when using shp2pgsql. There seems to be a
character
encoding issue also. The data was likely created on a computer
running
windows in Hebrew (reads right to left) and I am running ubuntu
in
English. Any help would be appreciated. I've pasted the output
below.
thank you in advance,
-david
--> shp2pgsql -s 2039 -W ISO-8859-8 URBANI.shp urban_areas |
psql
spatialtest
Shapefile type: Polygon
Postgis type: MULTIPOLYGON[2]
SET
BEGIN
NOTICE: CREATE TABLE will create implicit sequence
"urban_areas_gid_seq" for serial column "urban_areas.gid"
NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index
"urban_areas_pkey" for table "urban_areas"
CREATE TABLE
ERROR: permission denied for relation geometry_columns
CONTEXT: SQL statement "DELETE FROM geometry_columns WHERE
f_table_catalog = '' AND f_table_schema =
'public' AND
f_table_name = 'urban_areas' AND f_geometry_column = 'the_geom'"
PL/pgSQL function "addgeometrycolumn" line 94 at execute
statement
SQL statement "SELECT AddGeometryColumn('', $1 , $2 , $3 , $4 ,
$5 ,
$6 )"
PL/pgSQL function "addgeometrycolumn" line 4 at SQL statement
ERROR: current transaction is aborted, commands ignored until
end of
transaction block
ERROR: current transaction is aborted, commands ignored until
end of
transaction block
WARNING: nonstandard use of \' in a string literal
LINE 1: ...VALUES ('8.88287388491e+003','1.23130983450e
+006','בוענה נז
\...
^
HINT: Use '' to write quotes in strings, or use the escape
string
syntax (E'...').
ERROR: current transaction is aborted, commands ignored until
end of
transaction block
ERROR: current transaction is aborted, commands ignored until
end of
transaction block
ERROR: current transaction is aborted, commands ignored until
end of
transaction block
--> sudo tail -n 50 /var/log/postgresql/postgresql-8.2-main.log
2008-01-09 10:35:25 IST HINT: Use the escape string syntax for
escapes,
e.g., E'\r\n'.
2008-01-09 10:35:25 IST WARNING: nonstandard use of escape in a
string
literal at character 84
2008-01-09 10:35:25 IST HINT: Use the escape string syntax for
escapes,
e.g., E'\r\n'.
2008-01-09 10:49:05 IST NOTICE: CREATE TABLE will create
implicit
sequence "urban_areas_gid_seq" for serial column
"urban_areas.gid"
2008-01-09 10:49:05 IST NOTICE: CREATE TABLE / PRIMARY KEY will
create
implicit index "urban_areas_pkey" for table "urban_areas"
2008-01-09 10:49:05 IST ERROR: permission denied for relation
geometry_columns
2008-01-09 10:49:05 IST CONTEXT: SQL statement "DELETE FROM
geometry_columns WHERE
f_table_catalog = '' AND f_table_schema
=
'public' AND f_table_name = 'urban_areas' AND f_geometry_column
=
'the_geom'"
PL/pgSQL function "addgeometrycolumn" line 94 at execute
statement
SQL statement "SELECT AddGeometryColumn('', $1 , $2 ,
$3 , $4 ,
$5 , $6 )"
PL/pgSQL function "addgeometrycolumn" line 4 at SQL
statement
2008-01-09 10:49:05 IST STATEMENT: SELECT
AddGeometryColumn('','urban_areas','the_geom','2039','MULTIPOLYGON',2);
2008-01-09 10:49:05 IST ERROR: current transaction is aborted,
commands
ignored until end of transaction block
2008-01-09 10:49:05 IST STATEMENT: INSERT INTO
"urban_areas"
("shape_leng","shape_area","שם_הישוב","הערות","הסבר",the_geom) VALUES
('9.06802995385e+003','1.01826276165e+006','עילבון','ישוב',NULL,'SRID=2039;0106000
[text removed here] 1008BE226418C406025410C0D41F2C4D21C8CE22641');
2008-01-09 10:49:05 IST ERROR: current transaction is aborted,
commands
ignored until end of transaction block
___
postgis-users mailing list
postgis-users@postgis.refractions.net
http://postgis.refractions.net/mailman/listinfo/postgis-users
