[postgis-users] How to select points within circle
Hello, I have one table called building.sql which contains points. I want to select building which fall within a circle. This circle is not a sql table but instead only coordinate and radius such as circle(point '(0,0)', 2.0). My sql query is like this select gid, the_geom, floorarea from building as m where m.geom within(GeoFromText(circle(point '(306404701,6755737)', 200.0))); But it gives me erro. Does anyone knows where went wrong? Thanks a lot ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
Re: [postgis-users] How to select points within circle
Your syntax is a little backwards an it is better to use st_dwithin instead: select gid, the_geom, floorarea from building as m where ST_DWithin(m.geom,'POINT(306404701,6755737)'::geometry, 200.0); HTHNicklas 2010-10-19 zhang zhe wrote: Hello, I have one table called building.sql which contains points. I want to select building which fall within a circle. This circle is not a sql table but instead only coordinate and radius such as circle(point '(0,0)', 2.0). My sql query is like this select gid, the_geom, floorarea from building as m where m.geom within(GeoFromText(circle(point '(306404701,6755737)', 200.0))); But it gives me erro. Does anyone knows where went wrong? Thanks a lot ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
[postgis-users] create xml file from sql table
Hello, Does anyone knows how to generate xml file from postgres table. select table_to_xml(‘building3’,true,true,’’); will print xml file in data output . How can I generate a file, and save it to directory automatically. I want to write this function in php code. Thank you very much. ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
Re: [postgis-users] create xml file from sql table
Hi,
You can use psql to run your query from the command line.
psql -At -U {username} -c select
table_to_xml('building3',true,true,''); {database} query.xml
-A unaligned output (no formatting of results for display purposes)
-t Rows only (ie no header, so your xml file to only has xml in it)
If you don't really need an automated way you can use the Execute query
to file option in pgAdmin.
Regards,
Josh
From: postgis-users-boun...@postgis.refractions.net
[mailto:postgis-users-boun...@postgis.refractions.net] On Behalf Of
zhang zhe
Sent: Tuesday, 19 October 2010 9:37 PM
To: postgis-users@postgis.refractions.net
Subject: [postgis-users] create xml file from sql table
Hello,
Does anyone knows how to generate xml file from postgres table.
select table_to_xml('building3',true,true,'');
will print xml file in data output . How can I generate a file, and save
it to directory automatically. I want to write this function in php
code.
Thank you very much.
__
This email has been scanned by the MessageLabs Email Security System.
For more information please visit http://www.messagelabs.com/email
__
___
postgis-users mailing list
postgis-users@postgis.refractions.net
http://postgis.refractions.net/mailman/listinfo/postgis-users
Re: [postgis-users] How to select points within circle
Hello, Thanks. I try to run your query, and it saids parse error-invalid geometry. point (306404701,...) do you know where went wrong? Thanks Date: Tue, 19 Oct 2010 12:53:22 +0200 From: nicklas.a...@jordogskog.no To: postgis-users@postgis.refractions.net CC: Subject: Re: [postgis-users] How to select points within circle Your syntax is a little backwards an it is better to use st_dwithin instead: select gid, the_geom, floorarea from building as m where ST_DWithin(m.geom,'POINT(306404701,6755737)'::geometry, 200.0); HTH Nicklas 2010-10-19 zhang zhe wrote: Hello, I have one table called building.sql which contains points. I want to select building which fall within a circle. This circle is not a sql table but instead only coordinate and radius such as circle(point '(0,0)', 2.0). My sql query is like this select gid, the_geom, floorarea from building as m where m.geom within(GeoFromText(circle(point '(306404701,6755737)', 200.0))); But it gives me erro. Does anyone knows where went wrong? Thanks a lot ___ 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] create xml file from sql table
Hello,
Thanks. Do you know how to run this in php. I am writting php file which query
from postgres and produce xml file. Do you know how to do it with php?
Thanks
Date: Tue, 19 Oct 2010 21:54:11 +1000
From: josh.pres...@rpdata.com
To: postgis-users@postgis.refractions.net
Subject: Re: [postgis-users] create xml file from sql table
Hi,
You can use psql to run your query from the command line.
psql -At -U {username} -c “select table_to_xml(‘building3’,true,true,’’);”
{database} query.xml
-A unaligned output (no formatting of results for display purposes)
-t Rows only (ie no header, so your xml file to only has xml in it)
If you don’t really need an automated way you can use the “Execute query to
file” option in pgAdmin.
Regards,
Josh
From: postgis-users-boun...@postgis.refractions.net
[mailto:postgis-users-boun...@postgis.refractions.net] On Behalf Of zhang zhe
Sent: Tuesday, 19 October 2010 9:37 PM
To: postgis-users@postgis.refractions.net
Subject: [postgis-users] create xml file from sql table
Hello,
Does anyone knows how to generate xml file from postgres table.
select table_to_xml(‘building3’,true,true,’’);
will print xml file in data output . How can I generate a file, and save it to
directory automatically. I want to write this function in php code.
Thank you very much.
__
This email has been scanned by the MessageLabs Email Security System.
For more information please visit http://www.messagelabs.com/email
__
___ 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] How to select points within circle
Yes, sorry Should have been select gid, the_geom, floorarea from building as m where ST_DWithin(m.geom,'POINT(306404701 6755737)'::geometry, 200.0); /Nicklas 2010-10-19 zhang zhe wrote: Hello, Thanks. I try to run your query, and it saids parse error-invalid geometry. point (306404701,...) do you know where went wrong? Thanks Date: Tue, 19 Oct 2010 12:53:22 +0200 From: nicklas.a...@jordogskog.no To: postgis-users@postgis.refractions.net CC: Subject: Re: [postgis-users] How to select points within circle Your syntax is a little backwards an it is better to use st_dwithin instead: select gid, the_geom, floorarea from building as m where ST_DWithin(m.geom,'POINT(306404701,6755737)'::geometry, 200.0); HTHNicklas 2010-10-19 zhang zhe wrote: Hello, I have one table called building.sql which contains points. I want to select building which fall within a circle. This circle is not a sql table but instead only coordinate and radius such as circle(point '(0,0)', 2.0). My sql query is like this select gid, the_geom, floorarea from building as m where m.geom within(GeoFromText(circle(point '(306404701,6755737)', 200.0))); But it gives me erro. Does anyone knows where went wrong? Thanks a lot ___ 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] AddGeometryColumn BOX3D
Thank you Mike, you were absolutely right.
Now I am running into yet another problem with the box3d objects.
This command works fine:
INSERT INTO metadaten(box)
VALUES('BOX3D(3435970 5962930 -16.45,3439110 5967300 -3.28)'::BOX3D)
WHERE id=111;
When I try to do the same thing from within a python-script I get a no
binary input-function available for type box3d error. The original
error message is below this text, I took the liberty of translating it
into english.
I do not know whether it is the python-postgresql connector or a
database issue. To me it looks like the python connector can not handle
the box3d data-type.
DRIVER: postgresql.driver.pq3.Driver
http://pypi.python.org/pypi/py-postgresql/1.0.1
What I need to do is compute the box3d for about 150 datasets (point3d)
and save the boxes in a metadata table for fast access for later. Any
ideas how I could avoid doing this manually? It is just 2 commands, the
get box and the save box command but over and over again...
Regards
Jan
postgresql.exceptions.UndefinedFunctionError: keine binäre
Eingabefunktion verfügbar für Typ box3d
CODE: 42883
LOCATION: File 'lsyscache.c', line 2353, in getTypeBinaryInputInfo
from SERVER
RESULT:
type: SingleXactCopy
parameters: ('BOX3D(3421250 5968415 -22.96,3427355 5971725 -15.15)', 1)
STATEMENT: [prepared]
sql_parameter_types: ['public.box3d', 'INTEGER']
statement_id: py:0xb6eed3ac
string: Update metadaten SET box=$1::BOX3D WHERE id=$2
CONNECTION: [closed]
On 18.10.2010 17:14, Mike Toews wrote:
Hi Jan,
box3d is not a geometry, but it is it's very own type (along with
box2d). If you have pgAdminIII, you can browse these types (you may need
to enable visibility of types in the options), or if you use psql then
the command \dT will show all types.
Since it isn't a geometry, you can directly create it, e.g.:
create table foo(
id serial primary key,
box box3d
);
insert into foo(box) values('BOX3D(0 0 0, 10 10 10)'::box3d);
-Mike
___
postgis-users mailing list
postgis-users@postgis.refractions.net
http://postgis.refractions.net/mailman/listinfo/postgis-users
[postgis-users] ld error while building postgis-1.5
Hi, I am trying to build postgis 1.5 with geos-3.2.2, proj 4.7.0 and libxml-2.7.2 on WIndows 7. I am getting following error. I am using MinGW to build it. collect2: ld returned 5 exit status c:\MinGW\bin\dllwrap.exe: c:\MinGW\bin\gcc exited with status 1 make[1]: *** [postgis-1.5.dll] Error 1 make[1]: Leaving directory `/c/pginstaller.pune-repo/postgis.windows/postgis' make: *** [postgis] Error 2 Not getting any clue. Any help is appreciated. Please find attached complete build log Thanks in advance, -- Dharmendra Goyal Senior Software Engineer EnterpriseDB Corporation The Enterprise Postgres Company Phone: +91-20-30589493 Mobile: +91-9552103323 Website: http://www.enterprisedb.com EnterpriseDB Blog: http://blogs.enterprisedb.com/ Follow us on Twitter: http://www.twitter.com/enterprisedb This e-mail message (and any attachment) is intended for the use of the individual or entity to whom it is addressed. This message contains information from EnterpriseDB Corporation that may be privileged, confidential, or exempt from disclosure under applicable law. If you are not the intended recipient or authorized to receive this for the intended recipient, any use, dissemination, distribution, retention, archiving, or copying of this communication is strictly prohibited. If you have received this e-mail in error, please notify the sender immediately by reply e-mail and delete this message. postgis_build.rtf Description: RTF file ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
Re: [postgis-users] create xml file from sql table
I haven't used php myself, so I am not sure how you use it to talk to
postgres.
Josh
From: postgis-users-boun...@postgis.refractions.net
[mailto:postgis-users-boun...@postgis.refractions.net] On Behalf Of
zhang zhe
Sent: Tuesday, 19 October 2010 10:23 PM
To: postgis-users@postgis.refractions.net
Subject: Re: [postgis-users] create xml file from sql table
Hello,
Thanks. Do you know how to run this in php. I am writting php file
which query from postgres and produce xml file. Do you know how to do it
with php?
Thanks
Date: Tue, 19 Oct 2010 21:54:11 +1000
From: josh.pres...@rpdata.com
To: postgis-users@postgis.refractions.net
Subject: Re: [postgis-users] create xml file from sql table
Hi,
You can use psql to run your query from the command line.
psql -At -U {username} -c select
table_to_xml('building3',true,true,''); {database} query.xml
-A unaligned output (no formatting of results for display purposes)
-t Rows only (ie no header, so your xml file to only has xml in it)
If you don't really need an automated way you can use the Execute query
to file option in pgAdmin.
Regards,
Josh
From: postgis-users-boun...@postgis.refractions.net
[mailto:postgis-users-boun...@postgis.refractions.net] On Behalf Of
zhang zhe
Sent: Tuesday, 19 October 2010 9:37 PM
To: postgis-users@postgis.refractions.net
Subject: [postgis-users] create xml file from sql table
Hello,
Does anyone knows how to generate xml file from postgres table.
select table_to_xml('building3',true,true,'');
will print xml file in data output . How can I generate a file, and save
it to directory automatically. I want to write this function in php
code.
Thank you very much.
__
This email has been scanned by the MessageLabs Email Security System.
For more information please visit http://www.messagelabs.com/email
__
___ postgis-users mailing
list postgis-users@postgis.refractions.net
http://postgis.refractions.net/mailman/listinfo/postgis-users
__
This email has been scanned by the MessageLabs Email Security System.
For more information please visit http://www.messagelabs.com/email
__
___
postgis-users mailing list
postgis-users@postgis.refractions.net
http://postgis.refractions.net/mailman/listinfo/postgis-users
Re: [postgis-users] AddGeometryColumn BOX3D
will I lose data/precision if I save the box3ds as text first and once they are all in the column cast them to box3d? That operation would be purely inside the database and should work. should it? Regards Jan ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
Re: [postgis-users] create xml file from sql table
hello,
I tried your code
psql -At -U postgres -c “select table_to_xml(‘building3’,true,true,’’);”
postgres query.xml
but it gives erro
ident authentication failed for user postgres
what should be the user name. I tried with localhost, it gives the same error.
Do you have any idea?
Thanks
Date: Tue, 19 Oct 2010 21:54:11 +1000
From: josh.pres...@rpdata.com
To: postgis-users@postgis.refractions.net
Subject: Re: [postgis-users] create xml file from sql table
Hi,
You can use psql to run your query from the command line.
psql -At -U {username} -c “select table_to_xml(‘building3’,true,true,’’);”
{database} query.xml
-A unaligned output (no formatting of results for display purposes)
-t Rows only (ie no header, so your xml file to only has xml in it)
If you don’t really need an automated way you can use the “Execute query to
file” option in pgAdmin.
Regards,
Josh
From: postgis-users-boun...@postgis.refractions.net
[mailto:postgis-users-boun...@postgis.refractions.net] On Behalf Of zhang zhe
Sent: Tuesday, 19 October 2010 9:37 PM
To: postgis-users@postgis.refractions.net
Subject: [postgis-users] create xml file from sql table
Hello,
Does anyone knows how to generate xml file from postgres table.
select table_to_xml(‘building3’,true,true,’’);
will print xml file in data output . How can I generate a file, and save it to
directory automatically. I want to write this function in php code.
Thank you very much.
__
This email has been scanned by the MessageLabs Email Security System.
For more information please visit http://www.messagelabs.com/email
__
___ 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] How to select points within circle, thanks it works
Hello, thanks it works. BR, JoJo Date: Tue, 19 Oct 2010 12:53:22 +0200 From: nicklas.a...@jordogskog.no To: postgis-users@postgis.refractions.net CC: Subject: Re: [postgis-users] How to select points within circle Your syntax is a little backwards an it is better to use st_dwithin instead: select gid, the_geom, floorarea from building as m where ST_DWithin(m.geom,'POINT(306404701,6755737)'::geometry, 200.0); HTH Nicklas 2010-10-19 zhang zhe wrote: Hello, I have one table called building.sql which contains points. I want to select building which fall within a circle. This circle is not a sql table but instead only coordinate and radius such as circle(point '(0,0)', 2.0). My sql query is like this select gid, the_geom, floorarea from building as m where m.geom within(GeoFromText(circle(point '(306404701,6755737)', 200.0))); But it gives me erro. Does anyone knows where went wrong? Thanks a lot ___ 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] AddGeometryColumn BOX3D
Hi Jan, Here is a wrapper function to directly pass the double precision values to minimize any precision error due to text conversion: CREATE OR REPLACE FUNCTION ST_MakeBox3D(minx double precision, miny double precision, minz double precision, maxx double precision, maxy double precision, maxz double precision) RETURNS box3d AS 'SELECT ST_MakeBox3D(ST_MakePoint($1, $2, $3), ST_MakePoint($4, $5, $6))' LANGUAGE 'sql' IMMUTABLE STRICT COST 10; -- E.g. a cube: SELECT ST_MakeBox3D(3421250, 5968415, -22.96, 3427355, 5971725, -15.15); or from Python or other environment with parameter support, this will look more like: SELECT ST_MakeBox3D($1, $2, $3, $4, $5, $6); using all six inputs of double precision. -Mike On 19 October 2010 05:48, Jan Saalbach f...@dubmosphere.de wrote: will I lose data/precision if I save the box3ds as text first and once they are all in the column cast them to box3d? That operation would be purely inside the database and should work. should it? Regards Jan ___ 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] create xml file from sql table
From a user's standpoint, PostgreSQL (and PostGIS) does not write files. In order to read/write files, you need tools (psql, pgAdminIII, pgsql2shp, etc.). You need to write a custom tool for your situation in PHP. Ignoring details, you will need a database connection from PHP to PostgreSQL, run your query, and put the text result into PHP. Once PHP has this data, you can open a file in write mode and write the lines to the file. -Mike On 19 October 2010 04:37, zhang zhe luckycl...@hotmail.com wrote: Hello, Does anyone knows how to generate xml file from postgres table. select table_to_xml(‘building3’,true,true,’’); will print xml file in data output . How can I generate a file, and save it to directory automatically. I want to write this function in php code. Thank you very much. ___ 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.2 make check fails
On Tue, Oct 19, 2010 at 12:59:21PM -0700, Lee Hughes wrote: Running make check as root with PGUSER=postgres results in: could not access file /usr/local/src/postgis-1.5.2/regress/00-regress-install/lib/postgis-1.5: Permission denied in /tmp/pgis_reg_27083/regress_log I'm running this as root, and the /usr/local/src/postgis-1.5.2 directory tree is owned by root, so there should be no file permission issues. The directory usr/local/src/postgis-1.5.2/regress/00-regress-install/lib does exist. Any ideas? Is the directory accessible by the user running postgres server ? --strk; () Free GIS Flash consultant/developer /\ http://strk.keybit.net/services.html ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
Re: [postgis-users] PostGIS 1.5.2 make check fails
Temporarily granting full access for the postgres user to the regress directory tree did the trick -- thanks much Brent. Lee Quoting pcr...@pcreso.com: I'm no expert, but could this be related to root not having a postgresql account, or the postgres user actually running the check behind the scenes not having write access to that subdirectory? Brent Wood --- On Wed, 10/20/10, Lee Hughes hugh...@science.oregonstate.edu wrote: From: Lee Hughes hugh...@science.oregonstate.edu Subject: [postgis-users] PostGIS 1.5.2 make check fails To: postgis-users@postgis.refractions.net Date: Wednesday, October 20, 2010, 8:59 AM Running make check as root with PGUSER=postgres results in: could not access file /usr/local/src/postgis-1.5.2/regress/00-regress-install/lib/postgis-1.5: Permission denied in /tmp/pgis_reg_27083/regress_log I'm running this as root, and the /usr/local/src/postgis-1.5.2 directory tree is owned by root, so there should be no file permission issues. The directory usr/local/src/postgis-1.5.2/regress/00-regress-install/lib does exist. Any ideas? Thanks- Lee ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
[postgis-users] Database restore fails with missing liblwgeom
Restoring a pg_dumpall backup from a Postgres 8.1.3 + Postgis 1.1.4 server on a Postgres 9 + Postgis 1.5.2 server fails with could not access file $libdir/liblwgeom.so.1.1: No such file or directory Postgis compiled/installed fine, and make check passed all tests. There are no files like liblwgeom.so anywhere on the server, just liblwgeom.a and liblwgeom.h in /usr/local/src/postgis-1.5.2/liblwgeom/. Any ideas greatly appreciated. Thanks- Lee ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
