Re: [postgis-users] newbie query question
For ensuring there are no cloud occurences in a given roi I used except at first then realised having combined with count/groupby was the key. Having 0 occurence or in our case having maximum pixels clear - same either way. These queries get thorny - sqlalchemy orm made things alot easier for us becauses chances are that your having subquery needs to be joined to something for additional criteria. On 10/06/2010, at 5:02 AM, Mike Toews mwto...@gmail.com wrote: You could also use the EXCEPT set operator: SELECT * FROM tbl_nla EXCEPT SELECT * FROM tbl_nla JOIN tbl_rvb ON ST_DWithin(tbl_nla.the_geom, tbl_rvb.the_geom, 100); -Mike On 9 June 2010 11:19, Paul Ramsey pram...@cleverelephant.ca wrote: That's actually a surprisingly tricky question (to solve efficiently). The approach I have usually used is the counterintuitive one: do a left join on the positive constraint (*is* within 100 meters) and the return the rows that did *not* match the join (and therefore have null unique id values in the resultant). SELECT tbl_nla.gid FROM tbl_nla LEFT JOIN tbl_rvb ON ST_DWithin(tbl_nla.the_geom, tbl_rvb.the_geom, 100) WHERE tbl_rvb.gid IS NULL; P. On Wed, Jun 9, 2010 at 2:06 PM, G. van Es gves2...@yahoo.com wrote: I have two tables. tbl_nla has points as geometry and tbl_rvb has multipolygons. We want to list all the points of tbl_nla with no objects of tbl_rvb within 100 metres. Can anyone point me in the right direction? Thanks, Ge ___ 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 ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
Re: [postgis-users] No index usage on geography query plan?
Try making a copy of your wastac.t_tile_geometry_old table but with a geography instead of geometry column for border, and you should see an improvement. That's precisely what I showed in the original post - geography intersecting geography column. See the table def. I was outlining in this latter geometry example that a 9s intersection is not right and there is a problem with geograhies. ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
Re: [postgis-users] operator class gist_geometry_ops does not accept data type geography
Drop the opts from the parentheses for geog I think. On 14/05/2010, at 5:14 PM, rakesh modi rakesh.modi1...@gmail.com wrote: Hi All, I am newer to use postgres. I get an error using this query :- operator class gist_geometry_ops does not accept data type geography when i run query :- CREATE INDEX geom_idx ON routable USING GIST(the_geom GIST_GEOMETRY_OPS); What's the problem -- Rakesh Modi Software Engineer GIS Consortium ___ 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_CoveredBy supporting geography?
Thanks but not according to the doc I think?
1) Abstract superclass is mentioned in API, not point,
2) the SQL example uses circles
3) the blog link specifically uses polygons in it's discussion of OGC
coverage behaviour.
http://postgis.refractions.net/documentation/manual-1.5/ST_CoveredBy.html
I'm not disputing your explanation given the evidence, but suggest the
docs are entirely misleading for me figuring out if one polygon
geography is wholey inside another.
On 11/05/2010, at 11:48 PM, Paul Ramsey pram...@opengeo.org wrote:
Bad sentence construction in the error, perhaps? The restriction only
polygon and point means that one argument must be a polygon and one
must be a point. Both your arguments are polygons.
P.
On Tue, May 11, 2010 at 4:51 AM, Nicholas Bower n...@petangent.net
wrote:
I've read the 1.5 reference docs but can't figure out what is wrong
with
below. ST_CoveredBy(geography, geography) should work right?
select count(*) from t_swath_metadata where ST_CoveredBy(
ST_GeogFromText('SRID=4326;POLYGON((123.1773295292851
-16.07501950971949,
122.745660066063 -16.83965661151543,
124.1310486688905 -16.44978157737539,
123.1773295292851 -16.07501950971949))'),
swath_bounding);
ERROR: geography_covers: only POLYGON and POINT types are currently
supported
CONTEXT: SQL function st_covers statement 1
** Error **
ERROR: geography_covers: only POLYGON and POINT types are currently
supported
SQL state: XX000
Context: SQL function st_covers statement 1
db= \d t_swath_metadata;
...
swath_bounding| geography(Polygon,4326) |
Indexes:
...
t_swath_metadata_swath_bounding_key gist (swath_bounding)
Thanks, Nick
___
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] upgrading from 1.4 to 1.5.1
The docs are a bit rough in this area imo but I think it is assuming postgis 1.5 is installed (to libdir/postgis-1.5). Btw my experience is that it will also assume postgis 1.4 is installed to libdir/postgis. Confusingly when I ran 1.3 to 1.5 I found this and that I needed to have both installed to upgrade. On 20/04/2010, at 4:21 AM, P Kishor punk.k...@gmail.com wrote: Per the docs at http://www.postgis.org/documentation/manual-1.5/ch02.html#upgrading, I should find several postgis_upgrade*.sql files. But, it doesn't say where I should find them. A little bit of poking around revealed the following files to be inside the postgis directory punk...@lucknow ~/Projects/postgis-1.5.1$ll postgis .. -rw-r--r-- 1 punkish punkish 173460 Apr 19 12:51 postgis_upgrade_13_to_15.sql -rw-r--r-- 1 punkish punkish 173221 Apr 19 12:51 postgis_upgrade_14_to_15.sql -rw-r--r-- 1 punkish punkish 170268 Apr 19 12:51 postgis_upgrade_15_minor.sql So, I suggest the documentation be corrected to you should find several postgis_upgrade*.sql files in the 'postgis' folder. Then, I ran the following command, and got errors -- punk...@lucknow ~/Projects/postgis-1.5.1$psql -f postgis/postgis_upgrade_14_to_15.sql -d dbname BEGIN CREATE FUNCTION postgis_major_version_check -- Scripts versions checked for upgrade: ok (1 row) DROP FUNCTION psql:postgis/postgis_upgrade_14_to_15.sql:49: ERROR: could not access file $libdir/postgis-1.5: No such file or directory psql:postgis/postgis_upgrade_14_to_15.sql:53: ERROR: current transaction is aborted, commands ignored until end of transaction block and so on. Suggestions? -- 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] PostGIS 1.5.1 causing postmaster crash on index creation
Yes geography - migrated data from a geometry column (as per suggestion in other thread). I'll work on getting a dump for you to reproduce over the w/e. On 16/04/2010, at 12:32 AM, Paul Ramsey pram...@cleverelephant.ca wrote: Nicholas, Could you confirm that the crash is building an index on a *geography* column, not a *geometry* column? Ie, the problem has not been introduced to old geometry code, it's something in the new geography code. After that I'm going to need the data that causes the error... Paul On Thu, Apr 15, 2010 at 5:51 AM, Nicholas Bower n...@petangent.net wrote: Just upgraded to PostGIS 1.5.1 on a windows XP machine (from 1.3.4) and when creating an index on a geography column using this single statement; CREATE INDEX t_swath_metadata_swath_bounding_key ON wastac.t_swath_metadata USING gist (swath_bounding); ___ 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] Two solaris 10 compilation problems
Hi. Solaris 10 comes with a Postgresql 8.2 pre-installed, and I've used
blastwave to obtain the proj4 and geos libraries. Yet trying to compile
with latest Sun developer tools fails;
$ ./configure --with-pgsql=/usr/postgres/8.2/bin/pg_config
--with-proj=/opt/csw --without-geos
checking for gcc... /usr/bin/cc
...
SUMMARY
---
HOST_OS: solaris2.10
PGSQL: /usr/postgres/8.2/bin/pg_config
PROJ: prefix=/opt/csw libdir=/opt/csw/lib
ICONV: 1
PORTNAME: solaris
PREFIX: /usr/postgres/8.2
EPREFIX: ${prefix}
DOC: /usr/postgres/8.2/doc/contrib
DATA: ${datarootdir}
MAN: ${datarootdir}/man
BIN: /usr/postgres/8.2/bin
EXT: /usr/postgres/8.2/lib (\$$libdir)
---
[EMAIL PROTECTED] gmake
gmake -C lwgeom
gmake[1]: Entering directory `/apps/src/postgis/postgis-1.3.3/lwgeom'
cpp -P -traditional-cpp -DUSE_VERSION=82 -DUSE_PROJ lwpostgis.sql.in |
sed -e 's:@MODULE_FILENAME@:\$libdir/liblwgeom:g;s:@POSTGIS_VERSION@:1.3
USE_GEOS=0 USE_PROJ=1
USE_STATS=1:g;s:@POSTGIS_SCRIPTS_VERSION@:1.3.3:g;s/@POSTGIS_BUILD_DATE@/2008-07-24
03:16:58/g' | grep -v '^#' ../lwpostgis.sql
cpp -P -traditional-cpp -DUSE_VERSION=82 -DUSE_PROJ lwpostgis.sql.in |
sed -e '[EMAIL PROTECTED]@#\$libdir/liblwgeom#g;[EMAIL PROTECTED]@#1.3
USE_GEOS=0 USE_PROJ=1
USE_STATS=1#g;[EMAIL PROTECTED]@#1.3.3#g;s/@POSTGIS_BUILD_DATE@/2008-07-24
03:16:58/g' | grep -v '^#' ../regress/lwpostgis.sql
/opt/csw/bin/perl ../utils/postgis_proc_upgrade.pl ../lwpostgis.sql
../lwpostgis_upgrade.sql
/usr/bin/cc -DUSE_VERSION=82 -DPOSTGIS_LIB_VERSION='1.3.3'
-DPOSTGIS_VERSION='1.3 USE_GEOS=0 USE_PROJ=1 USE_STATS=1'
-DPOSTGIS_SCRIPTS_VERSION='1.3.3' -DPOSTGIS_BUILD_DATE='2008-07-24
03:16:58' -I/opt/csw/include -DUSE_PROJ
-I/usr/postgres/8.2/include/server -DAUTOCACHE_BBOX=1 -DUSE_STATS -Wall
-g -O2 -fPIC -fexceptions-c -o measures.o measures.c
cc: illegal option -Wall
gmake[1]: *** [measures.o] Error 1
gmake[1]: Leaving directory `/apps/src/postgis/postgis-1.3.3/lwgeom'
gmake: *** [liblwgeom] Error 2
cc is correctly detected, yet it's attempting a -Wall? That's problem
1, so I switched over to gcc...
$ export CC=/usr/sfw/bin/gcc; ./configure
$ gmake
$ sudo gmake install
[all ok]
yet when doing this...
$ /usr/postgres/8.2/bin/psql -d sedas -f ./lwpostgis.sql
psql:lwpostgis.sql:53: NOTICE: type histogram2d is not yet defined
DETAIL: Creating a shell type definition.
psql:lwpostgis.sql:53: ERROR: could not load library
/usr/postgres/8.2/lib/lib
lwgeom.so: ld.so.1: postgres: fatal: relocation error: file
/usr/postgres/8.2/l
ib/liblwgeom.so: symbol pj_set_searchpath: referenced symbol not found
$ crle
Configuration file [version 4]: /var/ld/ld.config
Default Library Path (ELF):
/lib:/usr/lib:/apps/common/lib:/usr/postgres/8.2/lib:/opt/csw/lib:/usr/sfw/lib
Trusted Directories (ELF):/lib/secure:/usr/lib/secure (system
default)
Any ideas? Thanks, Nick.
--
Nick Bower
[EMAIL PROTECTED]
___
postgis-users mailing list
postgis-users@postgis.refractions.net
http://postgis.refractions.net/mailman/listinfo/postgis-users
