[postgis-users] insert to database - error
Hi, Ubuntu 10.04 geoserver-2.0.2 postgresql 8.4 postgis I am experiencing the following: I can edit a postgis database geometry in Quantum GIS but I can´t "insert" into a postgis table inserted with shp3pgsql. ( I used the opengeo-suite windows version to upload the shp) my error is here message is here .. http://permalink.gmane.org/gmane.comp.gis.geoserver.user/26390 After days of scratching around the forums and reading this... http://lists.osgeo.org/pipermail/openlayers-users/2010-March/016801.html ..I decided to do the same a make a simple table in postgis - 1 x "id" column (type - integer, primary key, NotNull) and 1 x "the_geom" column ( type - geometry) I loaded it into geoserver and was able to start editing immediately Is it then something to do with the constraints inserted by shp2pgsql?..( I removed all of them one by one and tested the insert, but it still didn´t work) Possibly do to with the GID column? I ccan´t remove this column without breaking the geometry structure. Idon´t understand what the problem is with the table imported from a shapefile using shp2pgsql yours, Robert ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
Re: [postgis-users] speed of query
Puneet, I'm thinking you are doing a lot of extra calculations here by constantly transforming the points. Why did you turn a geometry into two points and then make the geometry again... this would invalidate the use of an index on the same column? (Neither did you define a SRID for your box in the first query) Have a look at : file:///usr/local/pgsql/share/doc/contrib/postgis-1.5/postgis.html#ST_MakeBox2D I think the query (slightly modified here) --Return all features that fall reside or partly reside in a US national atlas coordinate bounding box --It is assumed here that the geometries are stored with SRID = 2163 (US National atlas equal area) SELECT feature_id, feature_name, the_geom FROM features WHERE the_geom && st_transform(ST_SetSRID(ST_MakeBox2D(ST_Point(-91.048, 45.956), ST_Point(-90.973, 46.007)),4326),2163) is what you are after (obviously you will have to transform the lat long points (or the box. cheers Ben On 02/03/2011, at 3:40 AM, Puneet Kishor wrote: > I have a table with ~ 13.25 million points. > > CREATE TABLE ll ( > gid serial NOT NULL, > latitude double precision, > longitude double precision, > a integer, > b integer, > the_geom geometry, > CONSTRAINT ll_pkey PRIMARY KEY (gid), > CONSTRAINT enforce_dims_the_geom CHECK (st_ndims(the_geom) = 2), > CONSTRAINT enforce_geotype_the_geom CHECK ( > geometrytype(the_geom) = 'POINT'::text OR the_geom IS NULL > ), > CONSTRAINT enforce_srid_the_geom CHECK (st_srid(the_geom) = 2163) > ) > WITH ( > OIDS=FALSE > ); > > I want to select the columns a,b for the rows that lie within a box made by > points [-91.048, 45.956] and [-90.973, 46.007]. Here are my results -- > > Query 1 > > SELECT a, b FROM ll > WHERE > ST_Within( > ST_Point( > ST_X(ST_Transform(the_geom, 4326)), > ST_Y(ST_Transform(the_geom, 4326)) > ), > ST_MakeBox2D(ST_Point(-91.048, 45.956), ST_Point(-90.973, 46.007)) > ) > > 31 rows returned in 46125 ms > > Query 2 > > SELECT a, b FROM ll > WHERE > ST_X(ST_Transform(the_geom, 4326)) >= -91.048 AND > ST_X(ST_Transform(the_geom, 4326)) <= -90.973 AND > ST_Y(ST_Transform(the_geom, 4326)) >= 45.956 AND > ST_Y(ST_Transform(the_geom, 4326)) <= 46.007 > > 31 rows returned in 25729 ms > > Query 3 > > SELECT a, b FROM ll > WHERE > longitude >= -91.048 AND > longitude <= -90.973 AND > latitude >= 45.956 AND > latitude <= 46.007 > > 31 rows returned in 4011 ms > > Query 4 > > I also have the same data in a SQLite database with an R*Tree index on > lat/lon. A query analogous to Query 3 returns fast enough to not even > register a time... a few milliseconds; effectively 0 seconds. > > What gives? > -- > Puneet Kishor > > > > ___ > 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] ST_DWithin on 2 columns which are the same
Robert, On 01/03/2011, at 4:47 PM, robertvc wrote: > I have been trying, with no luck, to implement a query to return me all the > pairs of rows that are within a certain range of each other. I searched the > forum before trying to find a solution but I haven't been able to. Sorry if > there is a solution to this problem already that I didn't see. I think you need to search on nearest neighbour. > Suppose you have a 2 column table cars with an integer ID, and a geography > Location representing the position of the car globally. I want to write a > query that will return me pairs of IDs of cars that are within say 1km of > each other. Having looked at the functions available ST_DWithin seems the > obvious choice but I haven't been able to actually use it for what I want. > > I've started out by simply testing if it matches each car as being in range > with itself : > > SELECT * FROM cars WHERE ST_DWithin(location, location, 1); > > This returned all the entries from the cars table as expected (given that no > cars where actually within a meter of each other). This doesn't sound like my interpretation - it returned all the entries because each car's location was within 1 metre of itself (unsurprisingly). > I then tried to find all > the cars that are within a km of each other but don't have the same ID (to > avoid matching a car with itself). Because of this extra constraint I need > to somehow treat the location columns individually so I've tried the > following: > > SELECT c1.id, c2.id FROM cars AS c1, cars AS c2 WHERE > ST_DWithin(c1.location, c2.location, 1000) AND c1.id != c2.id; > > But this query never actually finishes computing (the number of entries in > my cars table is around 30k and after 2 hours of the query being executed I > still didn't get back a result). I would greatly appreciate any help in > computing this query as well as any tips on performance. I should also > probably mention that I did an indexing on the location column as suggested > in the documentation: > > CREATE INDEX cars_gix ON cars USING GIST (location); Did you also VACUUM ANALYZE after creating the index? Performance-wise, using geometry instead of geography might help. You could try EXPLAIN to see where the slow point in the query is. How widespread are the cars - are you talking about 30 000 cars that might all be within 1 km of each other? Given that the direction of the relation doesn't matter maybe use an outer join something like SELECT c1.id, c2.id FROM cars c1 LEFT OUTER JOIN cars c2 ON ST_DWithin(c1.location, c2.location, 1000) AND c2 IS NOT NULL AND c1.id <> c2.id; (I have no idea if that would work!) cheers Ben ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
Re: [postgis-users] speed of query
Hi, Indeed: ST_Point, ST_MakeBox2D and ST_Transform are IMMUTABLE, so the whole expression is computed once for all rows in this case. The only function called every time is ST_Within, which normally makes use of the spatial index. I wonder if the '&&' operator (ST_Overlaps) would be faster or not... JF 2011/3/1 Michael Smedberg : > OK, next guess. It looks like your SQL might be pretty inefficient. For > example, consider this SQL statement: > SELECT a, b FROM ll > WHERE > ST_Within( > ST_Point( > ST_X(ST_Transform(the_geom, 4326)), > ST_Y(ST_Transform(the_geom, 4326)) > ), > ST_MakeBox2D(ST_Point(-91.048, 45.956), ST_Point(-90.973, 46.007)) > ) > I think that says something like: > Look through the II table > For each row, transform the_geom to 4326, and take the X > For each row, transform the_geom to 4326, and take the Y > Make a point from that X,Y > Check if that point is within a box > I don't think that'll use an index, and I think it will do a bunch of > transformation work for every row. > I think that instead of transforming every row in II to 4326, you'd probably > be better served by transforming your bounding box to 2163 one time. I > think the SQL would look something like this: > > SELECT > a, > b > FROM > ll > WHERE > ST_Within( > the_geom, > ST_Transform( > ST_MakeBox2D( > ST_Point( > -91.048, > 45.956 > ), > ST_Point( > -90.973, > 46.007 > ) > ), > 2163 > ) > ) > > In any case, you should probably try looking at the output of EXPLAIN or > EXPLAIN ANALYZE to understand whether your index is being used, etc. > > > > > On Tue, Mar 1, 2011 at 11:40 AM, Puneet Kishor wrote: >> >> I have a table with ~ 13.25 million points. >> >> CREATE TABLE ll ( >> gid serial NOT NULL, >> latitude double precision, >> longitude double precision, >> a integer, >> b integer, >> the_geom geometry, >> CONSTRAINT ll_pkey PRIMARY KEY (gid), >> CONSTRAINT enforce_dims_the_geom CHECK (st_ndims(the_geom) = 2), >> CONSTRAINT enforce_geotype_the_geom CHECK ( >> geometrytype(the_geom) = 'POINT'::text OR the_geom IS NULL >> ), >> CONSTRAINT enforce_srid_the_geom CHECK (st_srid(the_geom) = 2163) >> ) >> WITH ( >> OIDS=FALSE >> ); >> >> I want to select the columns a,b for the rows that lie within a box made >> by points [-91.048, 45.956] and [-90.973, 46.007]. Here are my results -- >> >> Query 1 >> >> SELECT a, b FROM ll >> WHERE >> ST_Within( >> ST_Point( >> ST_X(ST_Transform(the_geom, 4326)), >> ST_Y(ST_Transform(the_geom, 4326)) >> ), >> ST_MakeBox2D(ST_Point(-91.048, 45.956), ST_Point(-90.973, 46.007)) >> ) >> >> 31 rows returned in 46125 ms >> >> Query 2 >> >> SELECT a, b FROM ll >> WHERE >> ST_X(ST_Transform(the_geom, 4326)) >= -91.048 AND >> ST_X(ST_Transform(the_geom, 4326)) <= -90.973 AND >> ST_Y(ST_Transform(the_geom, 4326)) >= 45.956 AND >> ST_Y(ST_Transform(the_geom, 4326)) <= 46.007 >> >> 31 rows returned in 25729 ms >> >> Query 3 >> >> SELECT a, b FROM ll >> WHERE >> longitude >= -91.048 AND >> longitude <= -90.973 AND >> latitude >= 45.956 AND >> latitude <= 46.007 >> >> 31 rows returned in 4011 ms >> >> Query 4 >> >> I also have the same data in a SQLite database with an R*Tree index on >> lat/lon. A query analogous to Query 3 returns fast enough to not even >> register a time... a few milliseconds; effectively 0 seconds. >> >> What gives? >> -- >> Puneet Kishor >> >> >> >> ___ >> 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 mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
Re: [postgis-users] speed of query
OK, next guess. It looks like your SQL might be pretty inefficient. For example, consider this SQL statement: SELECT a, b FROM ll WHERE ST_Within( ST_Point( ST_X(ST_Transform(the_geom, 4326)), ST_Y(ST_Transform(the_geom, 4326)) ), ST_MakeBox2D(ST_Point(-91.048, 45.956), ST_Point(-90.973, 46.007)) ) I think that says something like: Look through the II table For each row, transform the_geom to 4326, and take the X For each row, transform the_geom to 4326, and take the Y Make a point from that X,Y Check if that point is within a box I don't think that'll use an index, and I think it will do a bunch of transformation work for every row. I think that instead of transforming every row in II to 4326, you'd probably be better served by transforming your bounding box to 2163 one time. I think the SQL would look something like this: SELECT a, b FROM ll WHERE ST_Within( the_geom, ST_Transform( ST_MakeBox2D( ST_Point( -91.048, 45.956 ), ST_Point( -90.973, 46.007 ) ), 2163 ) ) In any case, you should probably try looking at the output of EXPLAIN or EXPLAIN ANALYZE to understand whether your index is being used, etc. On Tue, Mar 1, 2011 at 11:40 AM, Puneet Kishor wrote: > I have a table with ~ 13.25 million points. > > CREATE TABLE ll ( > gid serial NOT NULL, > latitude double precision, > longitude double precision, > a integer, > b integer, > the_geom geometry, > CONSTRAINT ll_pkey PRIMARY KEY (gid), > CONSTRAINT enforce_dims_the_geom CHECK (st_ndims(the_geom) = 2), > CONSTRAINT enforce_geotype_the_geom CHECK ( > geometrytype(the_geom) = 'POINT'::text OR the_geom IS NULL > ), > CONSTRAINT enforce_srid_the_geom CHECK (st_srid(the_geom) = 2163) > ) > WITH ( > OIDS=FALSE > ); > > I want to select the columns a,b for the rows that lie within a box made by > points [-91.048, 45.956] and [-90.973, 46.007]. Here are my results -- > > Query 1 > > SELECT a, b FROM ll > WHERE > ST_Within( > ST_Point( > ST_X(ST_Transform(the_geom, 4326)), > ST_Y(ST_Transform(the_geom, 4326)) > ), > ST_MakeBox2D(ST_Point(-91.048, 45.956), ST_Point(-90.973, 46.007)) > ) > > 31 rows returned in 46125 ms > > Query 2 > > SELECT a, b FROM ll > WHERE > ST_X(ST_Transform(the_geom, 4326)) >= -91.048 AND > ST_X(ST_Transform(the_geom, 4326)) <= -90.973 AND > ST_Y(ST_Transform(the_geom, 4326)) >= 45.956 AND > ST_Y(ST_Transform(the_geom, 4326)) <= 46.007 > > 31 rows returned in 25729 ms > > Query 3 > > SELECT a, b FROM ll > WHERE > longitude >= -91.048 AND > longitude <= -90.973 AND > latitude >= 45.956 AND > latitude <= 46.007 > > 31 rows returned in 4011 ms > > Query 4 > > I also have the same data in a SQLite database with an R*Tree index on > lat/lon. A query analogous to Query 3 returns fast enough to not even > register a time... a few milliseconds; effectively 0 seconds. > > What gives? > -- > Puneet Kishor > > > > ___ > 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] speed of query
On Tuesday, March 1, 2011 at 3:30 PM, Michael Smedberg wrote: > It looks like maybe you don't have an index on your the_geom column? If so, > you might want to read the "Indexing the data" section of > http://www.bostongis.com/PrinterFriendly.aspx?content_name=postgis_tut01 > I should have added the following information -- CREATE INDEX ll_the_geom_gist ON ll USING gist (the_geom); I do have an index. Ironically, I don't have an index on longitude and latitude columns, yet, searching on those columns is faster than on the_geom. > On Tue, Mar 1, 2011 at 11:40 AM, Puneet Kishor wrote: > > I have a table with ~ 13.25 million points. > > > > CREATE TABLE ll ( > > gid serial NOT NULL, > > latitude double precision, > > longitude double precision, > > a integer, > > b integer, > > the_geom geometry, > > CONSTRAINT ll_pkey PRIMARY KEY (gid), > > CONSTRAINT enforce_dims_the_geom CHECK (st_ndims(the_geom) = 2), > > CONSTRAINT enforce_geotype_the_geom CHECK ( > > geometrytype(the_geom) = 'POINT'::text OR the_geom IS NULL > > ), > > CONSTRAINT enforce_srid_the_geom CHECK (st_srid(the_geom) = 2163) > > ) > > WITH ( > > OIDS=FALSE > > ); > > > > I want to select the columns a,b for the rows that lie within a box made > > by points [-91.048, 45.956] and [-90.973, 46.007]. Here are my results -- > > > > Query 1 > > > > SELECT a, b FROM ll > > WHERE > > ST_Within( > > ST_Point( > > ST_X(ST_Transform(the_geom, 4326)), > > ST_Y(ST_Transform(the_geom, 4326)) > > ), > > ST_MakeBox2D(ST_Point(-91.048, 45.956), ST_Point(-90.973, 46.007)) > > ) > > > > 31 rows returned in 46125 ms > > > > Query 2 > > > > SELECT a, b FROM ll > > WHERE > > ST_X(ST_Transform(the_geom, 4326)) >= -91.048 AND > > ST_X(ST_Transform(the_geom, 4326)) <= -90.973 AND > > ST_Y(ST_Transform(the_geom, 4326)) >= 45.956 AND > > ST_Y(ST_Transform(the_geom, 4326)) <= 46.007 > > > > 31 rows returned in 25729 ms > > > > Query 3 > > > > SELECT a, b FROM ll > > WHERE > > longitude >= -91.048 AND > > longitude <= -90.973 AND > > latitude >= 45.956 AND > > latitude <= 46.007 > > > > 31 rows returned in 4011 ms > > > > Query 4 > > > > I also have the same data in a SQLite database with an R*Tree index on > > lat/lon. A query analogous to Query 3 returns fast enough to not even > > register a time... a few milliseconds; effectively 0 seconds. > > > > What gives? > > -- > > Puneet Kishor > > > > > > > > ___ > > 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] speed of query
It looks like maybe you don't have an index on your the_geom column? If so, you might want to read the "Indexing the data" section of http://www.bostongis.com/PrinterFriendly.aspx?content_name=postgis_tut01 On Tue, Mar 1, 2011 at 11:40 AM, Puneet Kishor wrote: > I have a table with ~ 13.25 million points. > > CREATE TABLE ll ( > gid serial NOT NULL, > latitude double precision, > longitude double precision, > a integer, > b integer, > the_geom geometry, > CONSTRAINT ll_pkey PRIMARY KEY (gid), > CONSTRAINT enforce_dims_the_geom CHECK (st_ndims(the_geom) = 2), > CONSTRAINT enforce_geotype_the_geom CHECK ( > geometrytype(the_geom) = 'POINT'::text OR the_geom IS NULL > ), > CONSTRAINT enforce_srid_the_geom CHECK (st_srid(the_geom) = 2163) > ) > WITH ( > OIDS=FALSE > ); > > I want to select the columns a,b for the rows that lie within a box made by > points [-91.048, 45.956] and [-90.973, 46.007]. Here are my results -- > > Query 1 > > SELECT a, b FROM ll > WHERE > ST_Within( > ST_Point( > ST_X(ST_Transform(the_geom, 4326)), > ST_Y(ST_Transform(the_geom, 4326)) > ), > ST_MakeBox2D(ST_Point(-91.048, 45.956), ST_Point(-90.973, 46.007)) > ) > > 31 rows returned in 46125 ms > > Query 2 > > SELECT a, b FROM ll > WHERE > ST_X(ST_Transform(the_geom, 4326)) >= -91.048 AND > ST_X(ST_Transform(the_geom, 4326)) <= -90.973 AND > ST_Y(ST_Transform(the_geom, 4326)) >= 45.956 AND > ST_Y(ST_Transform(the_geom, 4326)) <= 46.007 > > 31 rows returned in 25729 ms > > Query 3 > > SELECT a, b FROM ll > WHERE > longitude >= -91.048 AND > longitude <= -90.973 AND > latitude >= 45.956 AND > latitude <= 46.007 > > 31 rows returned in 4011 ms > > Query 4 > > I also have the same data in a SQLite database with an R*Tree index on > lat/lon. A query analogous to Query 3 returns fast enough to not even > register a time... a few milliseconds; effectively 0 seconds. > > What gives? > -- > Puneet Kishor > > > > ___ > 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] Securing postgis
Hi, I have a number of users each of which has their own schema. I don't want the users to be able to enable/disable the geospatialness of anyone else's columns. What's the right way to secure postgis so as to prevent this? It seems that a single, global, geometry_columns is the problem. I see a number of possibliities. If geometry_coulumns is all that needs to be secured I could create the table in each user's schema. If there's a lot of other infrastructure that needs to be duplicated this would not work as well -- the user's schemas would be all cluttered up. But I can see where having multiple geometry_columns tables could complicate an upgrade I could create a separate postgis schema for each user, but that seems overkill and I'm not at all clear on how $user is expanded in the search_path and whether or not it'd be possible to automatically have such schemas in the search path. then again I could just forget about it and hope the users don't kill each other. What's the best approach here? Thanks. Karl Free Software: "You don't pay back, you pay forward." -- Robert A. Heinlein ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
[postgis-users] speed of query
I have a table with ~ 13.25 million points. CREATE TABLE ll ( gid serial NOT NULL, latitude double precision, longitude double precision, a integer, b integer, the_geom geometry, CONSTRAINT ll_pkey PRIMARY KEY (gid), CONSTRAINT enforce_dims_the_geom CHECK (st_ndims(the_geom) = 2), CONSTRAINT enforce_geotype_the_geom CHECK ( geometrytype(the_geom) = 'POINT'::text OR the_geom IS NULL ), CONSTRAINT enforce_srid_the_geom CHECK (st_srid(the_geom) = 2163) ) WITH ( OIDS=FALSE ); I want to select the columns a,b for the rows that lie within a box made by points [-91.048, 45.956] and [-90.973, 46.007]. Here are my results -- Query 1 SELECT a, b FROM ll WHERE ST_Within( ST_Point( ST_X(ST_Transform(the_geom, 4326)), ST_Y(ST_Transform(the_geom, 4326)) ), ST_MakeBox2D(ST_Point(-91.048, 45.956), ST_Point(-90.973, 46.007)) ) 31 rows returned in 46125 ms Query 2 SELECT a, b FROM ll WHERE ST_X(ST_Transform(the_geom, 4326)) >= -91.048 AND ST_X(ST_Transform(the_geom, 4326)) <= -90.973 AND ST_Y(ST_Transform(the_geom, 4326)) >= 45.956 AND ST_Y(ST_Transform(the_geom, 4326)) <= 46.007 31 rows returned in 25729 ms Query 3 SELECT a, b FROM ll WHERE longitude >= -91.048 AND longitude <= -90.973 AND latitude >= 45.956 AND latitude <= 46.007 31 rows returned in 4011 ms Query 4 I also have the same data in a SQLite database with an R*Tree index on lat/lon. A query analogous to Query 3 returns fast enough to not even register a time... a few milliseconds; effectively 0 seconds. What gives? -- Puneet Kishor ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
Re: [postgis-users] Error when importing raster table
Hi. As you say the sql file has been generated, and i can load it. I was just curious why i got a warning? Thanks for the reminder. I will update my PostGIS.. Andreas 2011/3/1 Jorge Arévalo > On Tue, Mar 1, 2011 at 9:38 AM, Andreas Forø Tollefsen > wrote: > > Hi. > > As seen in a different post, I am working on some raster data. I have > > recently experienced a problem with processing of raster to tables using > the > > gdal2wktraster.py tool. > > Any idea why this gives and error, and is it just a warning about integer > vs > > float? > > C:\Python25>python gdal2wktraster.py -r > > c:\prio_grid\source\globcover\globlow.ti > > f -t globlow -s 4326 -k 50x50 -I -M -o globlow.sql > > gdal2wktraster.py:645: DeprecationWarning: integer argument expected, got > > float > > hexstr = binascii.hexlify(struct.pack(fmt_little, data)).upper() > > > > Summary of GDAL to WKT Raster processing: > > > > Number of processed raster files: 1 > > (c:\prio_grid\source\globcover\globlow.tif) > > List of generated tables (number of tiles): > > 1 globlow (45360) > > C:\Python25> > > > > > > > > > > > > ___ > > postgis-users mailing list > > postgis-users@postgis.refractions.net > > http://postgis.refractions.net/mailman/listinfo/postgis-users > > > > > > Hi Andreas, > > The SQL file has been generated. Could you load it into Postgres? > > Anyway, I strongly recommend you to use the new version of PostGIS > Raster (WKTRaster was the old name). Now PostGIS Raster is part of > PostGIS, and is packed with it (not official version yet, you need to > check it out from repository). The old version you're using is no > longer maintained > > Best regards, > > -- > Jorge Arévalo > Internet & Mobilty Division, DEIMOS > jorge.arev...@deimos-space.com > http://es.linkedin.com/in/jorgearevalo80 > http://mobility.grupodeimos.com/ > http://gis4free.wordpress.com > http://geohash.org/ezjqgrgzz0g > ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
Re: [postgis-users] Error when importing raster table
On Tue, Mar 1, 2011 at 9:38 AM, Andreas Forø Tollefsen wrote: > Hi. > As seen in a different post, I am working on some raster data. I have > recently experienced a problem with processing of raster to tables using the > gdal2wktraster.py tool. > Any idea why this gives and error, and is it just a warning about integer vs > float? > C:\Python25>python gdal2wktraster.py -r > c:\prio_grid\source\globcover\globlow.ti > f -t globlow -s 4326 -k 50x50 -I -M -o globlow.sql > gdal2wktraster.py:645: DeprecationWarning: integer argument expected, got > float > hexstr = binascii.hexlify(struct.pack(fmt_little, data)).upper() > > Summary of GDAL to WKT Raster processing: > > Number of processed raster files: 1 > (c:\prio_grid\source\globcover\globlow.tif) > List of generated tables (number of tiles): > 1 globlow (45360) > C:\Python25> > > > > > > ___ > postgis-users mailing list > postgis-users@postgis.refractions.net > http://postgis.refractions.net/mailman/listinfo/postgis-users > > Hi Andreas, The SQL file has been generated. Could you load it into Postgres? Anyway, I strongly recommend you to use the new version of PostGIS Raster (WKTRaster was the old name). Now PostGIS Raster is part of PostGIS, and is packed with it (not official version yet, you need to check it out from repository). The old version you're using is no longer maintained Best regards, -- Jorge Arévalo Internet & Mobilty Division, DEIMOS jorge.arev...@deimos-space.com http://es.linkedin.com/in/jorgearevalo80 http://mobility.grupodeimos.com/ http://gis4free.wordpress.com http://geohash.org/ezjqgrgzz0g ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
[postgis-users] ST_DWithin on 2 columns which are the same
Hi, I have been trying, with no luck, to implement a query to return me all the pairs of rows that are within a certain range of each other. I searched the forum before trying to find a solution but I haven't been able to. Sorry if there is a solution to this problem already that I didn't see. Suppose you have a 2 column table cars with an integer ID, and a geography Location representing the position of the car globally. I want to write a query that will return me pairs of IDs of cars that are within say 1km of each other. Having looked at the functions available ST_DWithin seems the obvious choice but I haven't been able to actually use it for what I want. I've started out by simply testing if it matches each car as being in range with itself : SELECT * FROM cars WHERE ST_DWithin(location, location, 1); This returned all the entries from the cars table as expected (given that no cars where actually within a meter of each other). I then tried to find all the cars that are within a km of each other but don't have the same ID (to avoid matching a car with itself). Because of this extra constraint I need to somehow treat the location columns individually so I've tried the following: SELECT c1.id, c2.id FROM cars AS c1, cars AS c2 WHERE ST_DWithin(c1.location, c2.location, 1000) AND c1.id != c2.id; But this query never actually finishes computing (the number of entries in my cars table is around 30k and after 2 hours of the query being executed I still didn't get back a result). I would greatly appreciate any help in computing this query as well as any tips on performance. I should also probably mention that I did an indexing on the location column as suggested in the documentation: CREATE INDEX cars_gix ON cars USING GIST (location); Thank you in advance, Robert -- View this message in context: http://old.nabble.com/ST_DWithin-on-2-columns-which-are-the-same-tp31038958p31038958.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] Error when importing raster table
Hi. As seen in a different post, I am working on some raster data. I have recently experienced a problem with processing of raster to tables using the gdal2wktraster.py tool. Any idea why this gives and error, and is it just a warning about integer vs float? C:\Python25>python gdal2wktraster.py -r c:\prio_grid\source\globcover\globlow.ti f -t globlow -s 4326 -k 50x50 -I -M -o globlow.sql gdal2wktraster.py:645: DeprecationWarning: integer argument expected, got float hexstr = binascii.hexlify(struct.pack(fmt_little, data)).upper() Summary of GDAL to WKT Raster processing: Number of processed raster files: 1 (c:\prio_grid\source\globcover\globlow.tif) List of generated tables (number of tiles): 1 globlow (45360) C:\Python25> ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
Re: [postgis-users] ST_Value from Polygon
Ok thanks. I got a bit confused regarding the vectorizing. Isnt that what i am doing with this query? I have now reduced the resolution to increase performance. Each cell is now 0.02224x0.02224. (previous X 8) SELECT gid, ((foo.geomval).val), CAST (SUM(ST_Area((foo.geomval).geom)) AS decimal(8,5)) as area, CAST(SUM(ST_Area((foo.geomval).geom))/0.25*100 AS decimal(6,4)) as percentarea INTO globlowrel FROM (SELECT priogrid_land.gid, ST_Intersection(globlow.rast, priogrid_land.cell) AS geomval FROM globlow, priogrid_land WHERE priogrid_land.cell && globlow.rast) AS foo WHERE (foo).gid >= 139300 AND (foo).gid <= 139301 GROUP BY gid, (foo.geomval).val; 2011/2/28 Pierre Racine > By reducing the tile size from 100x100 to 50x50 I get much better > performance. But still vectorizing is faster than counting pixels… > > > > *From:* postgis-users-boun...@postgis.refractions.net [mailto: > postgis-users-boun...@postgis.refractions.net] *On Behalf Of *Pierre > Racine > *Sent:* 28 février 2011 10:32 > *To:* Andreas Forø Tollefsen; PostGIS Users Discussion > > *Subject:* Re: [postgis-users] ST_Value from Polygon > > > > Andreas, > > > > Actually, since your raster coverage is tiled you should have used > ST_Intersects(priogrid_land.cell, globshort.rast) from the beginning. > > > > Understand that to do this query the system has to determine the value for > each single pixel (they are 7 231 680 000 ). Having precomputed > statistics, like the histogram for each tile, would not help as your polygon > grid do not necessarily correspond to the tile grid. Does it? > > > > The real solution, I think, lies in using a lower resolution (overview) of > the coverage to compute the statistics. The resulting numbers would be a bit > different but not really far from the one you get at highest resolution. You > would have to import your raster using the –l option and launch your query > on the overview table instead. > > > > I’m looking for a solution actually counting pixel values but it is still > slower than using ST_Intersection() (to my own surprise!). That means, for > now, it is faster to convert each tile to a vector representation (that’a > what ST_Intersection() is doing) and compute the areas of those polygons > than to count and summarize pixel values. Work in progress… > > > > Pierre > > > > *From:* Andreas Forø Tollefsen [mailto:andrea...@gmail.com] > *Sent:* 28 février 2011 04:48 > *To:* PostGIS Users Discussion > *Cc:* Pierre Racine; Paragon Corporation > *Subject:* Re: [postgis-users] ST_Value from Polygon > > > > I have reduced search time. It takes 534402 ms for 100 polygons now by > using a && operator in the intersection query. > > Having 64818 polygons this would take about 96 hours to complete. > > > > DROP TABLE IF EXISTS globshortrel; > > > > SELECT > > gid, > > ((foo.geomval).val), > > CAST (SUM(ST_Area((foo.geomval).geom)) AS decimal(8,5)) as area, > > CAST(SUM(ST_Area((foo.geomval).geom))/0.25*100 AS decimal(6,4)) as > percentarea, > > CAST (SUM(ST_Area((foo.geomval).geom))/0.077160617284 AS int) AS > npixels > > INTO globshortrel > > FROM (SELECT priogrid_land.gid, ST_Intersection(globshort.rast, > priogrid_land.cell) AS geomval FROM globshort, priogrid_land WHERE > priogrid_land.cell && globshort.rast) AS foo > > WHERE gid >= 139300 AND gid <= 139399 > > GROUP BY gid, (foo.geomval).val; > > > > Total query runtime: 534402 ms. > > > > 2011/2/28 Andreas Forø Tollefsen > > If I limit to only 10 polygons it uses 221285 ms. > > 2011/2/28 Andreas Forø Tollefsen > > > > Had the query running for over 48 hours but now it had crashed. Any idea on > how to optimize for speed and stability? > > > > DROP TABLE IF EXISTS globshortrel; > > > > SELECT > > gid, > > ((foo.geomval).val), > > CAST (SUM(ST_Area((foo.geomval).geom)) AS decimal(7,5)) as area, > > CAST(SUM(ST_Area((foo.geomval).geom))/0.25*100 AS decimal(6,4)) as > percentarea, > > CAST (SUM(ST_Area((foo.geomval).geom))/0.077160617284 AS int) AS > npixels > > INTO globshortrel > > FROM (SELECT globshort.rid, priogrid_land.cell, priogrid_land.gid, > ST_Intersection(globshort.rast, priogrid_land.cell) AS geomval FROM > globshort, priogrid_land) AS foo > > GROUP BY gid, (foo.geomval).val > > ORDER BY gid; > > > > > > -- SELECT * FROM globshortrel; > > > > -- SELECT DISTINCT ON(gid) gid, val, percentarea > > -- FROM globshortrel > > -- GROUP BY gid, val, percentarea > > -- ORDER BY gid, percentarea DESC > > ERROR: out of memory > > DETAIL: Failed on request of size 1753647. > > CONTEXT: SQL function "st_dumpaspolygons" statement 1 > > PL/pgSQL function "st_intersection" line 9 at RETURN QUERY > > SQL function "st_intersection" statement 1 > > > > ** Error ** > > > > ERROR: out of memory > > SQL state: 53200 > > Detail: Failed on request of size 1753647. > > Context: SQL function "st_dumpaspolygons" statement 1 > > PL/pgSQL function "st_intersection" line 9 at RETURN QUERY > > SQL function "st_intersection
