Re: [postgis-users] Difficult Problem with Polygons
On 29 October 2012 21:33, Ed Linde edoli...@gmail.com wrote: Hi All, I need help with 2 hard problems. I store triangles in a table as POLYGON. 1. I want to know for a given triangle, which triangles share an edge (adjacent) with this triangle. Sounds like you have a finite element mesh with nodes and elements. You can use ST_Relate with pattern 'FF2F11212' to pick out elements that share the same edge. This DE-9-IM is sort-of a custom ST_Touches, but only takes linear boundary overlaps. So if you have a table elements, and you want to find ones that touch ID 567: SELECT elements.* FROM elements, elements as e WHERE e.id = 567 AND ST_Relate(elements.geom, e.geom, 'FF2F11212'); I'm not certain about your second question. -Mike ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
Re: [postgis-users] PostgreSQL HStore and PostGIS
On 26 October 2012 12:26, Blair Deaver blairdea...@gmail.com wrote: Can anyone comment on successful or unsuccessful attempts to implement such a technology approach integrating PostGIS with HStore? Here are some links of interest that pair up PostGIS + HStore: http://www.stormdb.com/content/finding-dataset?destination=node%2F926 http://www.postgresonline.com/journal/archives/265-Schemas-vs.-Schemaless-structures-and-The-PostgreSQL-Type-Farm.html -Mike ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
Re: [postgis-users] Finding extra points
Sure, use linear referencing functions[1] to interpolate along a line between the two input points. For example, to find 10 points in-between 'POINT(2 8)' and 'POINT(3 5)': select ST_AsText(ST_Line_Interpolate_Point(ST_MakeLine(ST_MakePoint(2, 8), ST_MakePoint(3, 5)), f::float/10)) from generate_series(0,10) as f; Also, if you have a Z or M coordinate to also interpolate, you can add it as the third and fourth argument of ST_MakeLine, and you will see the interpolated result. These extra coordinate dimensions, for example, could be an attribute quantity from your source point data. Keep in mind, these are linear interpolation techniques. -Mike [1] http://postgis.refractions.net/documentation/manual-2.0/reference.html#Linear_Referencing On 26 October 2012 00:15, Jeff Lake j...@michiganwxsystem.com wrote: Greetings.. Is there a function or combination of functions in postGIS-2 that will help calculate points in between 2 given. I have imported a point shape file containing the National Hurricane Center's Watch/Warning breakpoints when they issue the warnings it contains at least 2 locations from this shape file. is there a way to calculate what points from the shape file would be in between the 2 given?? -- Jeff Lake MichiganWxSystem AllisonHouse GRLevelXStuff ___ 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] Invalid endian flag value encountered on Postgis 2.0.1, Postgres 9.x, Windows XP
I get the same invalid endian flag value encountered error with your
example on PostGIS 2.0. However, if I remove the escapes and use
dollar quoting instead, it works:
SELECT ST_AsEWKT(ST_GeomFromEWKB($$\001\001\000\000
\263\216\001\000\000\000\000\000\252\326'\301\000\000\000\000X\025.\301$$::bytea))
SRID=102067;POINT(-781141 -985772)
-Mike
On 20 October 2012 01:46, Václav Řehák rehak...@gmail.com wrote:
Hi,
I upgraded my development machine to Postgres 9.2 with Postgis 2.0.1
installed by the stack builder on Windows XP 32bit. My Django
application started to fail on a particular query which I nailed down
to the following problem:
select ST_GeomFromEWKB('\\001\\001\\000\\000
\\263\\216\\001\\000\\000\\000\\000\\000\\252\\326''\\301\\000\\000\\000\\000X\\025.\\301'::bytea);
ERROR: Invalid endian flag value encountered.
SQL state: XX000
The upgrade was performed by creating new db in PgAdmin using
template_postgis_20 and restoring from a backup with
postgis_restore.pl
I kept my previous Postgis 1.5.3 in Postgres 8.4 running on a
different port so I can test that the same WKB works there:
select st_astext(ST_GeomFromEWKB('\\001\\001\\000\\000
\\263\\216\\001\\000\\000\\000\\000\\000\\252\\326''\\301\\000\\000\\000\\000X\\025.\\301'::bytea))
POINT(-781141 -985772)
which is correct (in S-JTSK projection, SRID 102067).
I also tried Postgres 9.1 with both Postgis 1.5.5 and 2.0.1 but I
still get the endian error so I'm suspicious it has something to do
with Postgres version rather than Postgis.
Do you know how to check if the EWKB is correct? And what to check in
my Postgres setup?
Thanks.
Vaclav
___
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] Invalid endian flag value encountered on Postgis 2.0.1, Postgres 9.x, Windows XP
Oh, I just realized that your example would have worked fine with
escape strings prefixed with E:
select st_astext(ST_GeomFromEWKB(E'\\001\\001\\000\\000
\\263\\216\\001\\000\\000\\000\\000\\000\\252\\326''\\301\\000\\000\\000\\000X\\025.\\301'::bytea))
(note the above has a space character in the string sequence, not a
return character).
-Mike
On 20 October 2012 01:46, Václav Řehák rehak...@gmail.com wrote:
Hi,
I upgraded my development machine to Postgres 9.2 with Postgis 2.0.1
installed by the stack builder on Windows XP 32bit. My Django
application started to fail on a particular query which I nailed down
to the following problem:
select ST_GeomFromEWKB('\\001\\001\\000\\000
\\263\\216\\001\\000\\000\\000\\000\\000\\252\\326''\\301\\000\\000\\000\\000X\\025.\\301'::bytea);
ERROR: Invalid endian flag value encountered.
SQL state: XX000
The upgrade was performed by creating new db in PgAdmin using
template_postgis_20 and restoring from a backup with
postgis_restore.pl
I kept my previous Postgis 1.5.3 in Postgres 8.4 running on a
different port so I can test that the same WKB works there:
select st_astext(ST_GeomFromEWKB('\\001\\001\\000\\000
\\263\\216\\001\\000\\000\\000\\000\\000\\252\\326''\\301\\000\\000\\000\\000X\\025.\\301'::bytea))
POINT(-781141 -985772)
which is correct (in S-JTSK projection, SRID 102067).
I also tried Postgres 9.1 with both Postgis 1.5.5 and 2.0.1 but I
still get the endian error so I'm suspicious it has something to do
with Postgres version rather than Postgis.
Do you know how to check if the EWKB is correct? And what to check in
my Postgres setup?
Thanks.
Vaclav
___
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] service field in postgres
On 12 October 2012 06:17, Sandro Santilli s...@keybit.net wrote: You can leave that field blank. I've actually no idea what it is used for. From what I understand, it is used for a PostgreSQL service connection. See: http://hub.qgis.org/issues/3522 http://www.postgresql.org/docs/current/static/libpq-pgservice.html -Mike ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
Re: [postgis-users] Spatial Reference ID
From the pastebin text: NAD27 UTM Zone 10N http://spatialreference.org/ref/epsg/26710/ SRID=26710 Try it! -Mike On 10 October 2012 20:18, Ed Linde edoli...@gmail.com wrote: Hi All, I have this file I downloaded from geocomm and I am trying to figure out what the SRID for it in postgis would be. Have no idea. Can someone please help? Some sample x,y,z values in the file are 590283.357 5219156.537 658.84 590283.357 5219146.537 656.303 590283.357 5219136.537 660.872 590283.357 5219126.537 666.917 590283.357 5219116.537 671.999 The .txt file with spatial information -- http://pastebin.com/1H0zVYTQ I tried looking at http://spatialreference.org/. But could not figure out which one I should be using in Postgis. I basically want to transform that co-ordinate system into something more manageable where say each x,y,z value lies in the range of [0,1000] or something like that. Would be nice to hear some suggestions. Cheers, Ed ___ 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 install Postgresql + Postgis 2.0 on Ubuntu 12-04 LTS
On 3 October 2012 02:07, José María jmamu...@gmail.com wrote: Thanks Stefano, But when I try to follow the steps I get errors. If you could to install everything without problems Could you help me? Please. Thanks again, Hi José, I wrote those instructions, and I frequently test them out on new releases without problems. What are your errors? -Mike ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
Re: [postgis-users] where is postgis.sql?
On 2 October 2012 13:53, Vince Miller vincentpmil...@yahoo.com wrote: Thanks Mike, but your method produces the following message: ERROR: could not open extension control file /usr/share/postgresql/9.1/extension/postgis.control: No such file or directory. My OS is Ubuntu server 12.04, which includes with postgresql 9.1, which installs simply with apt-get install postgresql. Both GEOS and PostGIS 2.0 needs to be installed from source. Follow the instructions here: http://trac.osgeo.org/postgis/wiki/UsersWikiPostGIS20Ubuntu1204src Hope it works out! -Mike ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
Re: [postgis-users] where is postgis.sql?
Hi Vince, What operation system? What method did you install? If you have pg_config installed (part of the development package of postgres), you can find the share and contrib directory: $ ls `pg_config --sharedir`/contrib But, since you are using 9.1, you should use the extension method of attaching postgis. From psql/pgAdmin in your target database, use the DDL command: CREATE EXTENSION postgis; -Mike On 30 September 2012 06:28, Vince Miller vincentpmil...@yahoo.com wrote: Newby question: I want to attach postgis to my new db. Postgresql 9.1 seemed to install fine, but I can't find postgis.sql. Where is it? It's not in /usr/share/.../contrib as I'm given to expect. I don't want to compile from source to get it, if I don't have to. What's its relationship to postgis.sql.in.c? Thanks for your help. ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
Re: [postgis-users] SRID in geometry_columns view
On 25 July 2012 09:49, Richard Greenwood richard.greenw...@gmail.com wrote: I am having difficulty getting my views' SRIDs into the geometry_columns view in PostGIS 2.0. The doc's [1] suggest casting the geometry in the view so I tried: wkb_geometry::geometry(3739) which generates the error: ERROR: Invalid geometry type modifier: 3739 Yup, this invalid, but it is not what the manual says. The typmod is either: geometry(type,srid) or if SRID is not known, then geometry(type), where 'type' can be one of Geometry, Point, PointZ, etc, etc. next I tried: wkb_geometry::geometry(Geometry,3739) which generates the error: ERROR: cannot change data type of view column wkb_geometry from geometry to geometry(Geometry,3739) You need to either assign an SRID or reproject to that SRID. Are you geometries mixed? If not, you might want to use a more specific geometry type, like Polygon or MultiPolygon, etc., rather than Geometry. To assign a missing or incorrect SRID[1]: ALTER TABLE my_table ALTER COLUMN wkb_geometry TYPE geometry(Geometry,3739) USING ST_SetSRID(wkb_geometry,3739); Or if it needs to be transformed (reprojected) to a different SRID[2]: ALTER TABLE my_table ALTER COLUMN wkb_geometry TYPE geometry(Geometry,3739) USING ST_Transform(wkb_geometry,3739); -Mike [1] http://postgis.refractions.net/docs/ST_SetSRID.html [2] http://postgis.refractions.net/docs/ST_Transform.html ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
Re: [postgis-users] SRID in geometry_columns view
On 25 July 2012 13:48, Richard Greenwood richard.greenw...@gmail.com wrote: Okay, but there is an example in the doc's showing just geometry(srid) which doesn't work for me. Guess that's what you're saying? I now see what you are looking at, and it's a typo in the docs. I'm dealing with a view, not a table. My table shows the correct srid in geometry_columns but the view which is based upon the table shows a srid of 0. I don't want or need to transform the geometry. I just need for its srid to be correctly reflected in the geometry_columns view. OK, I follow you correctly now. You are using the older-style constraints on your table, which looks something like: ALTER TABLE my_table ADD CONSTRAINT enforce_srid_wkb_geometry CHECK (st_srid(wkb_geometry) = 3739); Although this shows the correct SRID for the table in the geometry_columns view, it doesn't propagate further to derived views. The simplest way to get this to work is to drop the older style constraint, and use the new 2.0 typmod syntax, described above. ALTER TABLE my_table DROP CONSTRAINT enforce_srid_wkb_geometry; ALTER TABLE my_table DROP CONSTRAINT enforce_geotype_wkb_geometry; -- you'll also need to temporarily drop your view; now, e.g. set as Point ALTER TABLE my_table ALTER COLUMN wkb_geometry TYPE geometry(Point,3739) USING ST_SetSRID(wkb_geometry,3739); After restoring your view, you should see the correct geometry type and SRID for the source table, and all derived views. -Mike ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
Re: [postgis-users] Error compiling postgis 2.0.1 in Centos 6.3
On 19 July 2012 08:44, Nahum Castro pedro1...@yahoo.com wrote: The problem with the postgresql.org repo is that do not have gdal and raster capabilities. Hi Nahum, A few days ago I finished some instructions for CentOS 6, using PGDG and EPEL. http://trac.osgeo.org/postgis/wiki/UsersWikiPostGIS20CentOS6pgdg GDAL is now mostly in PGDG (it wasn't before), but there are still a few missing dependencies for CentOS 6, which are supplied by EPEL. Devrim, I have in the instructions these [extra GDAL dependency] packages may be included as part of PGDG in future release, I'm not sure if that's true or not. Do you want a list of what else is missing? (I realize there is an impressive list of dependencies) -Mike ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
Re: [postgis-users] Error compiling postgis 2.0.1 in Centos 6.3
On 19 July 2012 11:28, Devrim GÜNDÜZ dev...@gunduz.org wrote: Yes, I am aware of those dependencies, and as you wrote, it is too many RPMs to copy to our repo -- so I'm not inclined to copy them, sorry :( OK, I've reworded instructions to reflect this situation with respect to CentOS. -Mike ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
Re: [postgis-users] spatialreference.org srid insert error
On 18 July 2012 07:19, Peter Tittmann ptittm...@gmail.com wrote: Seems a little strange that spatialreference.org would produce an invalid insert statement. Regardless, thanks for the pointer. Personally, I feel that spatialreference.org is a bit broke (but otherwise an excellent resource!). Firstly, it is broke with respect to the current version of PostGIS, where their SRID MAX_SRID, thus invalid. Secondly, their SRIDs are probably internal representations in their database, so they have really peculiar codes. For instance, if one were to use the definition of EPSG:4326 from http://spatialreference.org/ref/epsg/4326/postgis/ they wound need to use SRID=94326 to encode geometries, which is really odd and could raise some unexpected behaviour if it were converted to a geography type. So in general, try to set srid the same as auth_srid. -Mike ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
Re: [postgis-users] oddity with computing areas
Hi Eric, Are your geometries valid? See the following example to illustrate my point: with data as (select 'POLYGON((0 0, 1 0, 1 1, 0 1, 0 0))'::geometry AS geom1, 'POLYGON((0 0, 1 0, 1 1, 0 1, 0 0, 1 0, 1 1, 0 1, 0 0))'::geometry AS geom2) select st_isvalid(geom1), st_area(geom1), st_isvalid(geom2), st_area(geom2) from data; -Mike On 18 July 2012 09:12, Eric McKeeth eldi...@gmail.com wrote: Hello. I'm seeing behavior I don't understand when computing the area of the intersection of two geometries. Given the columns geom_a and geom_b, both MULTIPOLYGON geometries with the same SRID (4269 in this case), I'm selecting st_area(st_intersection(geom_a, geom_b)). In most cases, I'm getting results that look correct. But in a small percentage of cases, I'm getting cases where the area is much larger than expected (i.e. as much as 30x greater than the larger of st_area(geom_a) and st_area(geom_b)). It seems to me that it should always hold that the area of the smaller of two geometries should be the upper bound for the area of their intersection. I'm only seeing the issue in cases where the two geometries intersect, but neither contains the other. Does anyone have any ideas as to what could cause this? Thank you in advance for any help, -Eric ___ 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] oddity with computing areas
On 18 July 2012 09:46, Mike Toews mwto...@gmail.com wrote: with data as (select 'POLYGON((0 0, 1 0, 1 1, 0 1, 0 0))'::geometry AS geom1, 'POLYGON((0 0, 1 0, 1 1, 0 1, 0 0, 1 0, 1 1, 0 1, 0 0))'::geometry AS geom2) select st_isvalid(geom1), st_area(geom1), st_isvalid(geom2), st_area(geom2) from data; Hmm, on further analysis, valid geometries shouldn't matter as much for ST_Intersection: with data as (select 'POLYGON((0 0, 1 0, 1 1, 0 1, 0 0))'::geometry AS geom1, 'POLYGON((0 0, 1 0, 1 1, 0 1, 0 0, 1 0, 1 1, 0 1, 0 0))'::geometry AS geom2) select st_isvalid(st_intersection(geom1, geom1)), st_area(st_intersection(geom1, geom1)) from data; -MIke ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
Re: [postgis-users] Make a 3D table in PostGIS
If you are using PostGIS 2.0, you can use the typmod syntax by adding Z to the geometry type. For example, Point - PointZ: CREATE TABLE my3d( gid serial primary key, geom geometry(PointZ), name character varying(20) not null ); Then you can use functions like ST_MakePoint [1] to construct a 3D geometry to put in the table: INSERT INTO my3d(geom, name) SELECT ST_MakePoint(3.1, 4.2, 5.1), 'a 3D point'; [1] http://www.postgis.org/docs/ST_MakePoint.html -Mike On 16 July 2012 22:22, josamulai jmamu...@gmail.com wrote: Hello. How can to make a 3D table from a file with x, y, z information? Thank you. -- View this message in context: http://postgis.17.n6.nabble.com/Make-a-3D-table-in-PostGIS-tp4998971.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 mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
Re: [postgis-users] Bounding Box intersection
On 14 July 2012 00:35, Ed Linde edoli...@gmail.com wrote: Hi All, I have a table with linestring geometries, and I have the co-ordinates of a rectangle/ bounding box. I want to get only a restricted set of geometries that lie within or intersect with this box. Is there a way to construct this box on the fly and get only the relevant set of geometries? Hi Ed, You could use ST_MakeEnvelope [1] to make a box on the fly: SELECT * FROM roads WHERE geom ST_MakeEnvelope(10, 10, 11, 11, 4326); -Mike [1] http://postgis.refractions.net/docs/ST_MakeEnvelope.html ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
Re: [postgis-users] Casting a point and a polygon to geometry
On 10 July 2012 06:53, Roba Binyahib roba.binya...@kaust.edu.sa wrote: ST_Crosses(geometry g1, geometry g2); but I want g1 to be column of points and g2 a column of geometry, so how can I convert them to geometry enable to use the function By column of points, do you mean a PostGIS type, or a PostgreSQL type[1]? If it is a PostGIS type, then it is a geometry, with it's own internal type (Point, MultiPoint, Polygon, etc.). select r.name,u.id from river r, users u where ST_Crosses(r.point,u.polygon)='T' ; ST_Crosses returns T/F, so you don't need to evaluate ='T' . -Mike [1] http://www.postgresql.org/docs/current/static/datatype-geometric.html ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
Re: [postgis-users] Create topologic layers from simple geometries
On 27 June 2012 23:51, celati laurent lcel...@latitude-geosystems.com wrote: Hello Mike, I renaming my table Land_cover table to land_cover. This fixed the object naming problem; your new issue is not related. I have a new error message : INSERT INTO nei_topo(nei, topo) SELECT topology.toTopoGeom(the_geom, 'public', 1) FROM land_cover WHERE gid BETWEEN 1 and 8; ERREUR: INSERT a plus de colonnes cibles que d'expressions LINE 1: INSERT INTO nei_topo(nei, topo) Could you throw light for me? This is a basic SQL error saying that you want to insert data into two columns (nei, topo), but you are selecting three columns. Remove the last one, and it should work. -Mike ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
Re: [postgis-users] Fwd: geos_3.3.3-1_i386.changes ACCEPTED into unstable
On 24 May 2012 20:10, Paolo Cavallini cavall...@faunalia.it wrote: Done. Thanks Francesco Lovergine, and the Debian staff. Could someone update the howto? All the best. I've added a wiki page: http://trac.osgeo.org/postgis/wiki/UsersWikiPostGIS20Debian70src and updated the series: install PostGIS version X on your distro Y: http://trac.osgeo.org/postgis/wiki/UsersWikiInstall however, I haven't had a chance to test this method yet (bandwidth is limited where I'm at .. have to wait until next month to download the Debian 7.0 ISO). Basically, the instructions are identical as to the Debian 6.0 source installation, but the geos compile step is removed, and the version of PostgreSQL is adjusted. -Mike ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
Re: [postgis-users] Installing on Debian
On 21 May 2012 23:04, Paolo Cavallini cavall...@faunalia.it wrote: Hi all. The Debian package for 2.0 is still not done (any volunteer here?). Is there a clean install howto for Debian unstable/testing? I only found outdated/incomplete instructions. All the best, and thanks. Hi Paolo, Here are some simple instructions for Debian 6.0 (squeeze): http://trac.osgeo.org/postgis/wiki/UsersWikiPostGIS20Debian60src It looks like you still need to compile GEOS for PostGIS 2.0, since they are still packaging old versions for Debian sid. Mind you, the way these instructions work, the make test will fail on PostGIS 2.0 configured --with-raster, as there will be a second GEOS installed as a dependency for the libgdal-dev package. -Mike ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
Re: [postgis-users] use index for order by xmin(geom)
Hi Melchior, xmin is actually a PostgreSQL system column used for transactions, not a PostGIS function. http://www.postgresql.org/docs/current/static/ddl-system-columns.html The KNN nearest neighbour feature that I think you are describing is the - and # operators. Check out the documentation for more: http://postgis.refractions.net/docs/geometry_distance_centroid.html http://postgis.refractions.net/docs/geometry_distance_box.html -Mike On 17 May 2012 03:05, Melchior Moos melchior.m...@gmail.com wrote: I recently read that postgis 2.0 can use the index in order by clauses to find nearest neighbours of geometries. Is there also a way to use the index for queries like SELECT * FROM xy ORDER BY xmin(geom); ? Best regards, Melchior Moos ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
Re: [postgis-users] use index for order by xmin(geom)
On 17 May 2012 09:10, Melchior Moos melchior.m...@gmail.com wrote: Sorry, actually st_xmin is the function I'm after. I want to select the whole table ordered by minimum x coordinate of the gemertries. When I do it the naive way postgresql needs half an hour to prepare the ordering before the first results are delivered, since my table is quite large. I thought that it could be somehow possible to skip this time since the ordering is already stored in the index on the geometry column... One way to do this is to make an index using that function: CREATE INDEX mytable_xmin_idx ON mytable (ST_Xmin(geom)); Then check the planner to make sure the index is being used: EXPLAIN ANALYSE SELECT * FROM mytable ORDER BY ST_Xmin(geom); See if that speeds things up. -Mike ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
Re: [postgis-users] Closing polylines
On 12 May 2012 09:53, george wash gws...@hotmail.com wrote: Hi, does anyone know of a function or algorithm which will help me in closing open multilinestrings (contours) (I am using Postgis 2.0, Win 7 64). My contours are made up of several segments, all multilinestrings, which sometime are closed but often are open at several places along the same contour height. I can close them when the are open at the edge of the map by using a section of the edge as the closing segment but the real problem is when the contours are inside the map, e.g. when the several segments making up the contour do not share common start or end points but have a gap between them. Any suggestions would be most welcome. Thank you I use a custom function ST_ForceClosed to close anything that is not closed, including LineStrings or MultiLineStrings. This function can be added to any version of PostGIS: CREATE OR REPLACE FUNCTION ST_ForceClosed(geom geometry) RETURNS geometry AS $BODY$BEGIN IF ST_IsClosed(geom) THEN RETURN geom; ELSIF GeometryType(geom) = 'LINESTRING' THEN SELECT ST_AddPoint(geom, ST_PointN(geom, 1)) INTO geom; ELSIF GeometryType(geom) ~ '(MULTI|COLLECTION)' THEN -- Deconstruct parts WITH parts AS ( SELECT CASE WHEN NOT ST_IsClosed(gd.geom) AND GeometryType(gd.geom) = 'LINESTRING' THEN ST_AddPoint(gd.geom, ST_PointN(gd.geom, 1)) ELSE gd.geom END AS closed_geom FROM ST_Dump(geom) AS gd ) -- Reconstitute parts SELECT ST_Collect(closed_geom) INTO geom FROM parts; END IF; IF NOT ST_IsClosed(geom) THEN RAISE EXCEPTION 'Could not close geometry'; END IF; RETURN geom; END;$BODY$ LANGUAGE plpgsql IMMUTABLE COST 100; -- And example use with a LineString: WITH data AS (SELECT 'LINESTRING (190 370, 130 280, 177 205, 330 250, 290 330)'::geometry as linestr) SELECT ST_IsClosed(linestr), ST_ForceClosed(linestr), ST_IsClosed(ST_ForceClosed(linestr)) FROM data; -- Similar with MultiLineString: WITH data AS (SELECT 'MULTILINESTRING ((190 280, 165 209, 205 186, 260 240, 220 280), (170 320, 118 225, 150 120, 280 130, 310 250, 230 340))'::geometry as linestr) SELECT GeometryType(linestr), ST_IsClosed(linestr), ST_ForceClosed(linestr), ST_IsClosed(ST_ForceClosed(linestr)) FROM data; -Mike ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
Re: [postgis-users] building a polygon with variables
On 9 May 2012 02:26, Gold, Jack L (US SSA) jack.g...@baesystems.com wrote: I want to be able to replace l, b, r, and t with variable values in a plpgsql function like so: It looks like you want to make a polygon from 'left', 'bottom', 'right' and 'top'. Rather than formatting WKT, you could use a geometry constructor function: SELECT ST_MakeEnvelope(l, b, r, t, 4326); http://postgis.refractions.net/docs/ST_MakeEnvelope.html -Mike ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
Re: [postgis-users] POSTGIS Security
On 9 May 2012 04:45, Valerie Reinert vcrein...@gmail.com wrote: I've been told that POSTGIS has a new security package. Someone said it was called POSTGIS ES? Can anyone point me to the information on line or send me documentation. I think you heard about SE-PostgreSQL or Security-Enhanced PostgreSQL, which uses Security-Enhanced Linux, originally developed by the US NSA. Although I've never used it, I would think PostGIS would be compatible with the framework. Here is a good introduction to SE-PostgreSQL: http://wiki.postgresql.org/wiki/SEPostgreSQL_Introduction And and to SE-Linux: http://www.nsa.gov/research/selinux/ -Mike ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
Re: [postgis-users] DE-9IM question
On 9 May 2012 03:46, fork forkandw...@gmail.com wrote: Does anyone know of an exhaustive reference of DE-9IM matrices, with pictures? A really handy visual tool to work with DE-9IM is JTS TestBuilder. You can interact with the geometries and the results, which makes it a pretty good educational tool. You can copy/paste WKB directly into the geometry inputs, which makes it work nice with PostGIS. Take a look at http://gis.stackexchange.com/a/12119/1872 -Mike ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
Re: [postgis-users] Convert MySql to Postgres
On 6 May 2012 19:23, Smaran Harihar smaran.hari...@gmail.com wrote: Could you provide me with a good resource or guide me so that I can do this successfully the first time? I don't have any recent experience with this, but there is a pretty good list of free tools and scripts on the PostgreSQL wiki: http://wiki.postgresql.org/wiki/Converting_from_other_Databases_to_PostgreSQL#MySQL -Mike ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
Re: [postgis-users] Using PosGIS in one dimension
On 27 April 2012 00:05, Alvaro Tejero Cantero alv...@minin.es wrote: Hi Mike! Thank you. Your tip about loading numpy data into PostgreSQL will be key for my application (http://stackoverflow.com/questions/8144002/use-binary-copy-table-from-with-psycopg2) Great it's helpful! It probably deserves a better publishing platform than SO though. In fact, do you know about the reverse process (loading to numpy arrays)? It seems that Psycopg2 cannot do it but there is a project tackling that problem --- I don't know if this could eventually be incorporated into the mainstream driver (http://code.google.com/p/pgnumpy/ ). I've never heard of the package until now, but I'll check it out. There appears to be a sizable niche of presumably scientists that require linkage beteen numpy and postgres. (snip) That is what I was hoping for (only with less overhead from the 2nd, unused coordinate). Is it is possible to build the LINESTRING from the interval type on the fly for these operations (so as to keep the in-disk representation compact and also supportive of the exclude constraint)? I think you mean using an aggregate to combine many intervals from several rows into one. I don't see any aggregate functions for the range type, and as I mentioned, it wouldn't know how to express the union of two ranges that don't touch (i.e., a discontinuous range, which would require some MULTI* structure). PostGIS has several aggregates, like ST_Union and ST_MemUnion, all are pretty reliable and fast. Not sure about the exclude constraint, but I think it is based on the GiST index. -Mike ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
Re: [postgis-users] Using shp2pgsql
For future reference, when loading an SQL script with psql, to stop and exit on the first error, add the option: -v ON_ERROR_STOP=1 This is off by default, which is why you saw the repeated (and useless) error messages. With this option enabled, you will only see the first error, so you will know the real error. -Mike ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
Re: [postgis-users] Using PosGIS in one dimension
On 26 April 2012 07:41, David William Bitner bit...@gyttja.org wrote: If you follow PostgreSQL development, you can see that 9.2 is bringing a whole suite of range data types for dealing with one dimensional intervals with the same expressivity as PostGIS. Not there yet, but coming soon Yup, I'll certainly agree with David's suggestion of the new range type for 9.2. You can define integer ranges using int4range. The range type shares some of the same DB technology as PostGIS, such as GiST indices, so it is sort-of a 1D version of PostGIS. A really good article describing the upcoming range type is at: http://www.depesz.com/2011/11/07/waiting-for-9-2-range-data-types/ -Mike ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
Re: [postgis-users] Adding constraint
Add a check constraint with the logic:
ALTER TABLE test
ADD CONSTRAINT aaa_atrib_2_not_null CHECK (
CASE
WHEN attrib_1 = 'AAA'::text THEN attrib_2 IS NOT NULL
ELSE true
END);
-Mike
On 26 April 2012 08:34, Piotr Pachół piotrpac...@gmail.com wrote:
Hello,
Is it possible to add constraint to second attribute of table only in the
situation when first attribute of table equals specified value ?
I give an example:
CREATE TABLE test (
attrib_1 text,
attrib_2 text
);
INSERT INTO test VALUES ('AAA', 'QQQ');
INSERT INTO test VALUES ('BBB', 'PPP');
So is it possible to add constraint (for instance NOT NULL) to attrib_2
column if attrib_1 = 'AAA'
Regards,
Piotr
___
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] Using PosGIS in one dimension
On 26 April 2012 09:54, Alvaro Tejero Cantero alv...@minin.es wrote: David, Mike, thank you very much for this spot-on information! I have two follow-up questions: 1/ is there an estimated release date for pg 9.2? (in its stead: are Postgres pre-release versions usually 'stable enough' - I am in an explorative setting, rock-solid stability is not yet needed). Here is the roadmap: http://www.postgresql.org/developer/roadmap/ It looks like beta releases start soon, followed by final in Q3 of 2012. As the range type is new, I'm sure the postgres developers would love to have some beta testers. I had a play with the range type several months ago, and I found it functional. 2/ do these functions work with sets of intervals? I found an older post about an extension that seems to care for that http://scottrbailey.wordpress.com/2009/10/06/timespan_sets/ and I am concerned about how to do that efficiently (i.e. without a double loop on A intervals and B intervals). The range type has a whole host of set-style functions and operators, like union, difference, intersection, etc.: http://www.postgresql.org/docs/devel/static/functions-range.html One limitation of the range type that I came across is that there is no MULTI* equivalent. So you can have a continuous range of [4,10), but you can't punch a hole (6,7] within it (i.e., difference). To me, this difference yielding multi issue is conceptualized as: [4,10) - (6,7] = [4,6] _gap_ (7,10) .. can't work (throws an error), since there is no way to express a single range with a discontinuity. But with that thought, you could turn PostGIS into a similar 1D tool, using the MULTILINESTRING, using the x coordinate as your dimension, and ignoring all y coordinates. The above problem is: SELECT ST_AsText(ST_Difference( 'LINESTRING(4 0, 10 0)', 'LINESTRING(6 0, 7 0)')); st_astext --- MULTILINESTRING((4 0,6 0),(7 0,10 0)) (1 row) -Mike ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
Re: [postgis-users] compiling documentation
The Make targets are listed and described in the doc/README file: http://trac.osgeo.org/postgis/browser/trunk/doc/README -Mike ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
Re: [postgis-users] configure error: could not find gdal
On 18 April 2012 20:32, Simon Ortet simon.or...@gmail.com wrote: Hi, I'm trying to install postgis 2.0 from source and I get this error when running ./configure --with-pgconfig=/usr/local/pgsql/bin/pg_config: RASTER: Raster support requested checking for GDAL = 1.6.0... found checking gdal.h usability... yes checking gdal.h presence... yes checking for gdal.h... yes checking ogr_api.h usability... yes checking ogr_api.h presence... yes checking for ogr_api.h... yes checking cpl_conv.h usability... yes checking cpl_conv.h presence... yes checking for cpl_conv.h... yes checking for library containing GDALAllRegister... no configure: error: could not find gdal I tried with --with-gdalconfig=/usr/local/bin/gdal-config parameter as well (although /usr/local/bin is in the path anyways) with the same result. I'm running a Debian 6.0.4 64bits. GDAL is installed from source and gdal-config is in the path. A gdal-config --version command returns 1.9.0 Could it be that my gdal is missing something? Any idea? Your two options is to install GDAL: sudo apt-get install libgdal-dev Or configure without gdal: ./configure --without-raster -Mike ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
Re: [postgis-users] configure error: could not find gdal
On 18 April 2012 20:32, Simon Ortet simon.or...@gmail.com wrote: I'm running a Debian 6.0.4 64bits. GDAL is installed from source and gdal-config is in the path. A gdal-config --version command returns 1.9.0 Oh sorry, missed that. I've seen other issues with GDALAllRegister, so you aren't alone ... -Mike ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
Re: [postgis-users] configure error: could not find gdal
Here is one of the issues with GDALAllRegister: http://postgis.refractions.net/pipermail/postgis-users/2012-March/033043.html Do you remember the details of building GDAL? What version of GEOS do you have, and did you also install it from source for GDAL? $ geos-config --prefix $ geos-config --version -Mike ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
Re: [postgis-users] Strange ST_Area problem
On 25 March 2012 12:19, Eric Ladner eric.lad...@gmail.com wrote: Would EPSG:32230 be appropriate? (WGS 72, UTM zone 30N) I'm dealing with things roughly in the 30.0 to 31.0N range. EPSG:32630 is a better projected reference, since it shares the same spheroid, WGS 84, as EPSG:4326 (rather than mixing in the older WGS 72). http://spatialreference.org/ref/epsg/32630/ You should also check out converting your geometry types into geography types, which automagically calculates ST_Area (and ST_Length, and others) using tangible units of metres. http://postgis.refractions.net/docs/ch04.html#PostGIS_Geography -Mike ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
Re: [postgis-users] Removed double quotes from column and table names
On 21 March 2012 11:52, Simon Greener si...@spatialdbadvisor.com wrote: Thanks all for the suggestions. S If you are using postgres 9.0 or later, try something like this to rename tables: DO $$DECLARE r record; BEGIN FOR r IN (SELECT relname, regexp_replace(lower(relname), E'[ \-]+', '_', 'g') AS new_relname FROM pg_class c JOIN pg_namespace n ON n.oid=c.relnamespace WHERE n.nspname='public' AND relkind='r' AND relname quote_ident(relname)) LOOP EXECUTE 'ALTER TABLE ' || quote_ident(r.relname) || ' RENAME TO ' || quote_ident(r.new_relname) || ';'; END LOOP; END$$ A similar query can be used to rename columns. -Mike ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
Re: [postgis-users] creating a grid; transform error
On 16 March 2012 15:47, Puneet Kishor punk.k...@gmail.com wrote: I am trying to create a simple 1 deg x 1 deg grid and transform it to spherical mercator SELECT ST_Transform(ST_MakeEnvelope(minx, miny, maxx, maxy, 4326), 900913) the_geom FROM ( SELECT lng minx, lat miny, (lng + 1) maxx, (lat + 1) maxy FROM ( SELECT Generate_series(-180, 180, 1) lng, Generate_series(-90, 90, 1) lat ) series WHERE (lng + 1) 181 AND (lat + 1) 91 ) lat_lng I get ERROR: transform: couldn't project point (-180 -90 0): tolerance condition error (-20) What am I doing wrong? Transform has difficulties at the poles, since the limits of math are stretched. If you aren't gridding polar bears and/or penguins, then you should use only latitudes from 79S to 79N. Try this: SELECT ST_Transform(ST_MakeEnvelope(minx, miny, maxx, maxy, 4326), 900913) the_geom FROM ( SELECT lng minx, lat miny, (lng + 1) maxx, (lat + 1) maxy FROM ( SELECT Generate_series(-180, 179, 1) lng, Generate_series(-89, 88, 1) lat ) series ORDER BY lng, lat ) lat_lng; ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
Re: [postgis-users] creating a grid; transform error
On 16 March 2012 16:48, pcr...@pcreso.com wrote: Because the Mercator projects the poles at infinity, Google Maps cannot show the poles. Instead it cuts off coverage at 85° north and south. Brent Wood This looks like sound advice. You can update your query to respect that latitude range using: Generate_series(-85, 84, 1) lat -Mike ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
Re: [postgis-users] creating a grid; transform error
On 16 March 2012 16:52, Puneet Kishor punk.k...@gmail.com wrote: Thanks Mike... works. I just noticed there was something funny going on with the generate_series cross product, so it wasn't producing as many rows. It should be: SELECT ST_Transform(ST_MakeEnvelope(minx, miny, maxx, maxy, 4326), 900913) the_geom FROM ( SELECT lng minx, lat miny, (lng + 1) maxx, (lat + 1) maxy FROM generate_series(-85, 84, 1) lat, generate_series(-180, 179, 1) lng ORDER BY lng, lat ) lat_lng; -Mike ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
Re: [postgis-users] update a geometry field from real fields from postgis datatable
You can avoid float8 - text - float8 conversions with something like:
update mytable set
geom = ST_SetSRID(ST_MakePoint(lon, lat), 4326)
(I'm guessing SRID=4326, but yours could be different)
-Mike
On 14 March 2012 09:52, francis francis.mil...@free.fr wrote:
hello,
I have a table with two columns 'lat' and 'lon' : it is real type double
and a column 'geom' type geometry;
how to make a request to update my column geom from 'lat' and 'lon'
columns ?
somewhat I wrote this :
update mytable set geom = 'POINT (' | | lat | | '' | | lon | | ')';
but it does not work!
Do you have an idea?
thank you
my config
Windows XP Pro SP2
PostGIS 1.5.3
--
___
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] Spatial column from text via a VIEW, Possible?
On 7 March 2012 06:10, Bob Basques bob.basq...@ci.stpaul.mn.us wrote: ... ST_AsText(ST_Transform(ST_SetSRID(ST_MakePoint(part3::numeric, part4::numeric),4326)), 200068) as geom_city ... ERROR: function st_transform(geometry) does not exist LINE 4: ST_AsText(ST_Transform(ST_SetSRID(ST_MakePoint(pa... It looks like 4326)), 200068) should be 4326), 200068)) -Mike ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
Re: [postgis-users] Calling pgsql2shp.exe via python
On 21 February 2012 13:39, David Quinn daithiqu...@gmail.com wrote: Thanks for the link, Stefan. After testing out a few approaches this worked: import subprocess subprocess.call(['C:/Program Files (x86)/PostgreSQL/9.1/bin/pgsql2shp.exe', '-f', 'D:\testShapefile.shp', '-h localhost', '-u postgres', '-p 5434', 'test_db', 'SELECT * FROM myschema.testquery']) Watch out for the escaping for Windows paths. You should have one of these: 'D:\\testShapefile.shp' r'D:\testShapefile.shp' (I prefer this method) 'D:/testShapefile.shp' (I think this works) As you have it, it will be D:[tab]estShapefile.shp with the tab character. I don't think you are allowed to have a tab character in Windows path names, but I'm not sure. -Mike ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
Re: [postgis-users] importing shapefile error
On 16 February 2012 10:17, Zelio Fernandes zelio.f...@gmail.com wrote:
zelio@zelio-desktop:~$ /usr/lib/postgresql/9.1/bin/shp2pgsql -s 4326 -i -I
/home/zelio/Desktop/Final(2)/FOREST.shp public.FOREST | psql -d prakasam
bash: syntax error near unexpected token `('
zelio@zelio-desktop:~$
This has nothing to do with PostGIS; it is a syntax error in BASH due
the '(' and ')'. The normal practice to naming file path arguments is
to use double quotes around files, e.g.
command some file(2).shp
More info:
http://www.gnu.org/software/bash/manual/bashref.html#Quoting
-Mike
___
postgis-users mailing list
postgis-users@postgis.refractions.net
http://postgis.refractions.net/mailman/listinfo/postgis-users
Re: [postgis-users] radius of buffer parameter issue at st_buffer function...
Are you sure your coordinates are projected? Could you get an example EWKT from you dataset using: SELECT ST_AsEWKT(geom) FROM mytable LIMIT 1; If you are seeing something like POINT(147.65625 -33.046875), which has linear units of degrees (not metres), then you need to properly set the SRID and transform them: SELECT ST_AsEWKT(ST_Transform(ST_SetSRID(ST_geom, 4326), 3308)) FROM mytable LIMIT 1; Note: I'm only guessing 4326 for WGS84, but your data could use a different datum. If your geometries are indeed projected, and the linear distances make sense, make sure your geometries are valid: SELECT * FROM mytable WHERE NOT ST_IsValid(geom); Invalid geometries cause havoc with some operations. -Mike On 18 January 2012 12:45, ZHAO Gorton gorton.z...@rms.nsw.gov.au wrote: I am using postgis 1.5 and use the st_buffer function with my spatial queries. I have set the srid to 3308 for my spatial data which the measurement unit is meter, but when I use the st_buffer function and set the radius of buffer in meter. The spatial queries return far more rows than what I expected. The measurement unit of radius of buffer in st_buffer function does not seem to me to be in meter. Instead of using 10 for a radius of 10 meters, I have to use a value of 10*1.7453292519943283E-5 instead. What could be the problem with this st_buffer function? I do not mind to use this value but it is not what postgis documented. It would be appreciated if you could help on this. Regards, Gorton ZHAO ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
Re: [postgis-users] new functions
Also another comment for your transrotate function: you can use 1 call to ST_Affine, rather than 3 indirect calls with st_rotate(st_translate(st_rotate(. You just need to wrangle the trigonometry in the affine transformation matrix correctly. I have an enhancement for a st_rotate with a point of origin in trac, where you can see how to use ST_Affine for this purpose: http://trac.osgeo.org/postgis/ticket/1251 -Mike ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
Re: [postgis-users] Error on updating geometry column from two columns value
ST_GeomFromText turns WKT into a geometry, not SQL. You can either
format WKT (text) using:
ST_GeomFromText('POINT(' || NEW.koordinat_x || ', ' || NEW.koordinat_y
|| ')', 32748)
Or, a simpler/faster/lossless geometry constructor would be to pass
the floating point values directly to a point geometry:
ST_SetSRID(ST_MakePoint(NEW.koordinat_x, NEW.koordinat_y), 32748)
Note: you had your X/Y coordinates the other way around. PostGIS
coordinates are always ordered X/Y or long/lat.
-Mike
On 1 December 2011 20:31, Firman Hadi jalmibur...@gmail.com wrote:
Dear all,
I want to create one table with 3 columns (x, y, geom). I want to input the
x and y using form with PHP.
When I submit the form, it will trigger the new row.
I use this step below but when I insert the data I get the error as in
attachment.
I hope that anyone can help me to solve the problem.
Thank you in advance.
Kind regards,
Firman Hadi
Center for Remote Sensing - ITB
Indonesia
CREATE TABLE try_geometry (
koordinat_y integer,
koordinat_x integer,
geom geometry PRIMARY KEY
);
CREATE FUNCTION try_geometry_func () RETURNS trigger AS '
BEGIN
NEW.geometri = ST_GeomFromText('POINT(NEW.koordinat_y,
NEW.koordinat_x)',32748);
RETURN NEW;
END;
' LANGUAGE plpgsql;
===
CREATE TRIGGER try_geom_trg BEFORE INSERT OR UPDATE
ON coba FOR EACH ROW
EXECUTE PROCEDURE try_geometry_func ();
___
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] Building GEOS 3.3.1 on Ubuntu
Skip the autogen.sh step, since you didn't download via SVN (that's what the instructions say). Here are your steps for Ubuntu from the geos-3.3.1 directory: ./configure make make check sudo make install sudo ldconfig -Mike On 27 November 2011 18:47, Aren Cambre a...@arencambre.com wrote: I realize this is not the GEOS email group, but 3.3.1 appears to be a prereq for running PostGIS 2.0? Ubuntu's most recent GEOS package is 3.2.2. I can't get the builds to work. Below is a copy of the documentation bug report I filed with the GEOS developers. What can be suggested to them to get their instructions fixed? Or am I doing something wrong? Aren ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
Re: [postgis-users] Problems installing PostGIS 2.0SVN on PostgreSQL 9.1
On 18 November 2011 17:11, webs...@web.de wrote: cd postgis-2.0.0SVN ./configure --with-pgconfig=/usr/local/pgsql/bin/pg_config make make install It's a good idea to also run a [sudo] ldconfig command here createdb db createlang plpgsql db When loading postgis.sql definitions with... psql -d db -h localhost -p 5432 -U postgres -f /usr/local/pgsql/share/contrib/postgis-2.0/postgis.sql There should only be one error, and you can stop psql on that with by adding -v ON_ERROR_STOP=1 to your command ERROR: could not load library /usr/local/pgsql/lib/postgis-2.0.so: /usr/local/pgsql/lib/postgis-2.0.so: undefined symbol: GEOSRelatePatternMatch STATEMENT: CREATE OR REPLACE FUNCTION spheroid_in(cstring) RETURNS spheroid AS '$libdir/postgis-2.0','ellipsoid_in' LANGUAGE 'C' IMMUTABLE STRICT; This looks like your error. What version of GEOS do you have? For PostGIS 2.0 you need GEOS 3.2.2 or higher. -Mike ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
Re: [postgis-users] The use of substring()
On 24 October 2011 15:01, Andy Colson a...@squeakycode.net wrote:
Assuming your data looks something like:
filename
---
high pass 1/1/2011.ecw
low pass 5/13/1999.ecw
I'd use something like:
update raster set date_of_creation = substring(filename from
'\d+/\d+/\d+')::timestamp;
Of course, being from Germany, your date is probably 13/5/1999, and maybe the
::timestamp cast will convert it ok, but maybe not.
If, however, your date is in the form 5 apr 1999, that's a bit tougher. But
you didnt give any details! Woot! so I'm off the hook. :-)
Specifically formatted date/timestamp casts can be made with to_date
or to_timestamp functions:
http://www.postgresql.org/docs/current/static/functions-formatting.html
Examples:
select to_date(substring('high pass 13/5/1999.ecw' from
E'\\d+\\/\\d+\\/\\d+')), 'DD/MM/');
select to_date(substring('high pass 5 apr 1999.ecw' from
E'\\d+ \\w+ \\d+'), 'DD mon ');
Note: you need to do the funny looking E'\\d'-like escaping; see also:
http://www.postgresql.org/docs/current/static/functions-matching.html
-Mike
___
postgis-users mailing list
postgis-users@postgis.refractions.net
http://postgis.refractions.net/mailman/listinfo/postgis-users
Re: [postgis-users] representing 3D shapes, e.g. gml:AbstractSolidType in PostGIS?
On 14 October 2011 05:49, Courtin Olivier olivier.cour...@oslandia.com wrote: Global aim, is to have a 3D topological library. So something acting like GEOS, but for surfaces and solid. First step will be to look closer if available 3D library could be an help, yes or no. (December 2011 - February 2012) A volumetric library similar to GEOS would be very nice, as I seem to deal with these problems often. (I wish I could have funding resources for this). The literature seems to be recently advancing on topological models: http://www.inf.bv.tum.de/papers/uploads/paper_0629.pdf http://www.sciencedirect.com/science/article/pii/S1474034609000287 (the later peer-reviewed journal article even mentions PostGIS!) -Mike ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
Re: [postgis-users] Why is my PostGIS query not using a spatial index?
What version of PostGIS/PostgreSQL? With 1.5/9.0, my explain analyze results are different than yours, where line 4 from EXPLAIN ANALYZE for both queries show: Index Scan using geoplanet_place_bbox_id on geoplanet_place -Mike ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
Re: [postgis-users] ST_Transform troubles
On 27 June 2011 19:54, Frans Knibbe frans.kni...@geodan.nl wrote: To me the argument We are ignoring the standard because everyone else is doing the same comes across as rather weak. The whole point of standards is that you comply with them, otherwise they are useless. I fully agree that having different axis orders is a big nuisance. But not complying with standards can be a nuisance too. I think it is well established among most (all?) spatial DBs that comply with OGC Standards that the coordinate order is Cartesian: X Y or Longitude Latitude. If there is any issues with this, then it is with the OGC Standards you disagree with. Mind you, this is only an internal ordering in WKB, that you may occasionally see in WKT. Both of these are computer markup languages, not designed to look pretty in a report. However, if you do want to make the coordinate look more human readable, it is rather simple to write your own function to report coordinates in any style you like. An interesting case study of following OGC Standards for coordinate ordering is with MS SQL Server 2008 Spatial's GEOGRAPHY type. When the preview was released, coordinate order of WKT was Lat Lon, but was reversed to Lon Lat for the subsequent preview and final release. See the following links for more: http://social.msdn.microsoft.com/forums/en-US/sqlspatial/thread/41250c42-25e6-4de7-953e-a6c41ada383f/ http://blogs.msdn.com/b/isaac/archive/2007/12/27/latitude-longitude-ordering.aspx http://www.spatiallyadjusted.com/2007/12/27/microsoft-turns-the-world-right-side-up-again/ -Mike ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
Re: [postgis-users] ST_Transform troubles
On 24 June 2011 01:19, Frans Knibbe frans.kni...@geodan.nl wrote: POINT(6.86264236062518 53.3160795502069) There are two things wrong with this result: 1) The coordinates are in the wrong order (EPSG:4326 uses latitude, longitude). They are in the correct order. Standards say X, Y which are long, lat. This convention is commonly confused, as lat, long is very common. 2) There are too much significant numbers in the result (the implied accuracy was increased by ST_Transform). It's precision (not accuracy) that was increased. This is generally a good thing, and is required to represent global positions within fractions of a millimeter. The significant digits method of determining precision does not work here as the actual re-projection calculations are not simple. I would have expected a result like POINT(53.31608 6.86264) You can format geometry any way you like, e.g. for reporting as 53.31608N 6.86264E. But if you are passing data for applications, keep to standard WKT and high precision if you can. The distance between the high-precision and 5-decimal precision is about 16.5 cm, which can be significant to many users. -Mike ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
Re: [postgis-users] terminated server connection during st_isvalid()
On 8 June 2011 01:26, Birgit Laggner birgit.lagg...@vti.bund.de wrote: But I am still curious why this crashes the server connection... It looks like a bug. With a recent SVN version of PostGIS on PG 8.4 here is the moment before the crash: NOTICE: [lwgeom.c:lwgeom_release:571] releasing type Polygon NOTICE: [lwgeom.c:lwgeom_release:576] lwgeom_release: releasing bbox. 0xa1f6b18** Error ** -Mike ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
Re: [postgis-users] Enter details into Geom column
If you have a table named CLEANEDCAMDENGPS (in public), and you want
a column GPS_POINTS:
SELECT AddGeometryColumn('CLEANEDCAMDENGPS', 'GPS_POINTS', 4326, 'POINT', 2);
works fine. There is some confusion in your first message with another
table CAMDENGPS and another geometry column geom. Also, just be
warned that although you can name tables/columns with mixed and upper
case, I've found it to be more trouble than it is worth. For instance,
you will always need to use double quotes for these entities:
SELECT ST_X(GPS_POINTS), ST_Y(GPS_POINTS)
FROM CLEANEDCAMDENGPS
LIMIT 10;
You can easily rename them in pgAdmin to a lowercase equivalent, which
doesn't require quoting.
-Mike
On 31 May 2011 06:35, James Smith james.david.sm...@gmail.com wrote:
Dear Brent,
Thank you for your reply and simple explanation, it's much appreciated.
Unfortunately, it doesn't seem to work. When I try to create the Geom column,
I get this error:
-
ERROR: function st_creategeometrycolumn(unknown, unknown, unknown, integer,
unknown, integer) does not exist
SQL state: 42883
Hint: No function matches the given name and argument types. You might need
to add explicit type casts.
Character: 8
-
So I browsed the PostGIS functions, and thought that I should perhaps use the
function 'AddGeometryColumn' instead, so changed the code to below:
select AddGeometryColumn('public', 'CLEANEDCAMDENGPS','geom',4326,'POINT',2);
However this returns an error of the below:
ERROR: relation public.CLEANEDCAMDENGPS does not exist
I should perhaps say at this point, that the table called CLEANEDCAMDENGPS is
within a database called CAMDENGPS. I played around with trying to put the
database name into the statement too, but with no luck.
Any thoughts?
Thanks again, and yes, I'll take onboard your point about captials and table
names from this point forwards.
Cheers
James
On 29 May 2011 23:44, pcr...@pcreso.com wrote:
Hi James,
I suggest you avoid upper case letters in table column names if you can.
It makes a few things easier
The syntax in both SQL statements is wrong. Try:
select
ST_CreateGeometryColumn('public','CLEANEDCAMDENGPS','geom',4326,'POINT',2);
the fields are (in order):
schema where table can be found ('public')
the table name where you want the new column ('CLEANEDCAMDENGPS')
the name of the geometry column to create ('geom')
the SRID of the geometry column to create (4326)
the geometry type ('POINT')
the number of dimensions (2 - x y)
All string values need to be quoted.
To populate this column try:
update CLEANEDCAMDENGPS
set geom=setsrid(makepoint(LONGITUDE,LATITUDE),4326);
So, create a point geometry from the two numeric columns (makepoint), force
the SRID of this geometry to 4326 (setsrid), write this value to your new
column (update table set column =).
HTH,
Brent Wood
James Smith wrote:
Dear all,
Would appreciate some help. I have created an existing database (with
PostGIS extension) and it has a table called CLEANEDCAMDENGPS which
is populated with approx 600,000 rows. There are 20 or so columns in the
table, two of which are Latitude and Longitude (WGS84). I would now
like to create a Geom column with points in, the values of which
should be taken from the latitude and longitude column. Could someone
provide me with sample code as to how to do this please? I had a go
with the below, but don't really know what I'm doing... neither of the
statements work...
--CREATE THE COLUMN--
SELECT AddGeometryColumn('CAMDENGPS', 'GPS_POINTS', 'geom', 4326, 'POINT',
2)
--POPULATE THE COLUMN--
INSERT INTO CLEANEDCAMDENGPS (GPS_POINTS)
VALUES ( ST_GEOMFROMTEXT('POINT((SELECT LONGITUDE FROM
CLEANEDCAMDENGPS) (SELECT LATITUDE FROM CLEANEDCAMDENGPS))', 4326,
'Point'));
Thank you
James
___
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] [postgis-devel] In-place upgrade to 2.0 (New features in postgreSQL 9.1. - trigram)
On 25 May 2011 11:47, Paragon Corporation l...@pcorp.us wrote: Paul That's really excellent, and boy I wish I knew what file I should be reading to know all these new features... what file should I be reading? Probably the best one to look at is this one since it has examples as well. http://wiki.postgresql.org/wiki/What%27s_new_in_PostgreSQL_9.1 Another great resource is dpesz's blog WAITING FOR 9.1, which showcases all the important features with good examples and comments within each post. http://www.depesz.com/index.php/tag/pg91/ -Mike ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
Re: [postgis-users] ogr dxf
Here is the main resource: http://www.gdal.org/ogr/drv_dxf.html And Frank's blog regarding the driver: http://fwarmerdam.blogspot.com/search/label/dxf -Mike On 20 May 2011 06:13, Bob Pawley rjpaw...@shaw.ca wrote: Hi Has anyone used OGR to convert DXF to Postgis and Postgis to DXF? Perhaps, you could point me to a site that will help me. So far I haven’t been able to find much information. Bob ___ 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] encryption of PostGIS databases
Hi Paul, This is more of a PostgreSQL question, see: http://www.postgresql.org/docs/9.0/static/encryption-options.html You can require that all connections use SSL and make sure all users have strong passwords. -Mike On 13 May 2011 00:30, Malm Paul paul.m...@saabgroup.com wrote: Hi, Is there a way to protect geographical data by encrypt the GIS db, if so that is the impact on performance? Kind regards, Paul ___ 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] GeomFromText in a user function.
On 27 April 2011 05:46, Charles E. Deaton cdea...@corp.realcomp.com wrote:
I have been trying to create a user function that I can call as needed by
passing in bbox coordinates.
Are you trying to select items in a box-shaped geometry? There are a
few better ideas to construct a simple box polygon that use fewer than
eight parameters:
-- Three numeric parameters, using a box centre (x,y) and expand
distance of 1:
SELECT ST_AsEWKT(ST_Expand(ST_MakePoint(15,25), 1));
-- One parameter, using the bounding box from a geometry
SELECT ST_AsEWKT(Box2D('POLYGON ((10 40, 30 20, 40 50, 10
40))'::geometry)::geometry);
Your problem is due to a misinterpretation of the messy concatenation.
Try using dollar-quote literals e.g.: $BODY$This text doesn't do
much$BODY$. Postgres thinks you are trying to use a type named
geomfromtext, which is not your intent. I generally try to avoid
constructing geometries into text using string concatenation as they
are subject to fail if one of the parameters is null, and they are
extra overhead (float - string - parse to float) with possible loss
of precision.
-Mike
___
postgis-users mailing list
postgis-users@postgis.refractions.net
http://postgis.refractions.net/mailman/listinfo/postgis-users
Re: [postgis-users] please help convert raster problem
pgAdmin has a confusing behavior to show a blank data cell if the number of characters is above some threshold in size. You can query the character length of a text result to see if it at leasts exists in some form: select character_length(st_astext(rast)) from tablename; hopefully you'll see a big number. -Mike On 1 April 2011 16:18, Eman Sayed eman_saye...@yahoo.com wrote: hello, i am using mapserver for windows (ms4w) ,postgres8.4,postgis2.0 and pmapper template i converted a jpg(1.45 MB) into postgis by this commands: aster2pgsql.py -r 555.jpg -t tablename -o image.sql psql -h localhost -U postgres -d template_postgis -f image.sql it is take much time to convert and create one raster table with one row but when i open the table on postgres give me that empty table or postgres backend crash and i find nothing on the map with no errors can any body help me please ___ 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] a couple of procedural questions for populating tables programmatically
On 9 March 2011 03:07, Mr. Puneet Kishor punk.k...@gmail.com wrote:
I have a couple of procedural questions. I am populating my table with point
and cell geometries programmatically. First question: When I INSERT values
other than the geometry in rows and then UPDATE the geometry in a second
process, everything works --
$sth = $dbh-prepare(qq{INSERT INTO base.cells (x, y) VALUES (?, ?)});
for (@a) {
$sth-execute($a[0], $a[b]);
}
$sth = $dbh-prepare(qq{
UPDATE base.new_cells
SET
the_point = GeomFromText('POINT(' || x || ' ' || y || ')', 2163),
the_cell = GeomFromText(
'POLYGON((' ||
(x - $csmid) || ' ' || (y - $csmid) || ',' ||
(x + $csmid) || ' ' || (y - $csmid) || ',' ||
(x + $csmid) || ' ' || (y + $csmid) || ',' ||
(x - $csmid) || ' ' || (y + $csmid) || ',' ||
(x - $csmid) || ' ' || (y - $csmid) || '))',
2163
)
});
$sth-execute;
However, if I try to do the above in one attempt like so --
$sth = $dbh-prepare(qq{INSERT INTO base.cells (x, y, the_point, the_cell)
VALUES (?, ?, ?, ?)});
for (@a) {
$sth-execute(
$a[0],
$a[b],
GeomFromText('POINT(' || $a[0] || ' ' || $a[y] || ')', 2163),
If you want to avoid precision loss from double-text conversions, try
setting the_point constructor with:
ST_SetSRID(ST_MakePoint($a[0], $a[y]), 2163)
(or is $a[y] really $a[b]? Why is $a[0] not $a[x]? I'm not sure)
GeomFromText(
'POLYGON((' ||
(x - $csmid) || ' ' || (y - $csmid) || ',' ||
(x + $csmid) || ' ' || (y - $csmid) || ',' ||
(x + $csmid) || ' ' || (y + $csmid) || ',' ||
(x - $csmid) || ' ' || (y + $csmid) || ',' ||
(x - $csmid) || ' ' || (y - $csmid) || '))',
2163
There is easily a problem somewhere here. I'm not sure how exactly x,
y, and $csmid are used here, but they can't refer to the SQL columns
x or y yet, since this is an INSERT statement. A similar binary
method to above can be combined with ST_Expand:
ST_Expand(ST_SetSRID(ST_MakePoint($a[0], $a[y]), 2163), $csmid)
I get an error saying the method GeomFromText doesn't exist. Would like to
understand the reason behind this.
Your full error message possibly says something like
ERROR: function geomfromtext(unknown) does not exist
because the datatype your are passing to GeomFromText is not text, and
another function for any other datatype does not exist.
Second question: I am doing the following to set the SRID (besides specifying
it in the UPDATE step above).
INSERT INTO geometry_columns (f_table_catalog, f_table_schema,
f_table_name, f_geometry_column, coord_dimension, srid, type)
VALUES ('', 'base', 'cells', 'the_point', 2, 2163, 'POINT');
INSERT INTO geometry_columns (f_table_catalog, f_table_schema,
f_table_name, f_geometry_column, coord_dimension, srid, type)
VALUES ('', 'base', 'cells', 'the_cell', 2, 2163, 'POLYGON');
Is the above correct and sufficient?
Looks correct, but it only needs to be done once, when you make the
table/columns. Don't insert this after each UPDATE step above.
-Mike
___
postgis-users mailing list
postgis-users@postgis.refractions.net
http://postgis.refractions.net/mailman/listinfo/postgis-users
Re: [postgis-users] meters to degrees
You are using EPSG:3044, correct? http://spatialreference.org/ref/epsg/3044/ http://spatialreference.org/ref/epsg/3044/This projection has units of metres. ST_DWithin and related functions use the same units of length as defined in the projection, which in this case is metres, not degrees. You may need to elaborate with an example. -Mike On 7 March 2011 23:41, michal nagy lachim1...@gmail.com wrote: Well, it sitll does not solve my problem. As I said I am using geometry(planar) with SRID 3044, where units are degrees, therefore when using st_dwithin will still take input in degrees. 2011/3/7 Paragon Corporation l...@pcorp.us Use ST_DWithin instead. http://www.postgis.org/documentation/manual-1.5SVN/ST_DWithin.html We cover this topic in detail in the first chapter of our book which is a free download. http://www.postgis.us/chapter_01 Leo and Regina -- *From:* postgis-users-boun...@postgis.refractions.net [mailto: postgis-users-boun...@postgis.refractions.net] *On Behalf Of *michal nagy *Sent:* Monday, March 07, 2011 2:29 AM *To:* postgis-users@postgis.refractions.net *Subject:* [postgis-users] meters to degrees Hello everyone, I am new to Postgres and PostGIS. I have encountered one problem, that I can not deal with.I have table with various points that have SRID=3044, which should be UTM zone including Jutland. I have created function to loop through all points in table and check if they are within given radius(using geometry and function st_point_inside_circle). Now to the problem, for some reason st_point_inside_circle takes radius in degrees, which is very inconvenient for me. I would really need to change to metres. If anyone has a suggestion, pls let me know. Thank you for help Michal ___ 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] meters to degrees
Hi Michal,
It looks like you declared your data in location.point using
longitude/latitude, rather than easting/northing. If you want to store
your data in projected EPSG:3044, you need to first transform it from
geographic units, e.g.:
SELECT ST_AsEWKT(ST_Transform(ST_SetSRID(ST_MakePoint(7.4545, 44.0),
4326), 3044));
looks like
SRID=3044;POINT(376090.362129178 4873033.87926193)
Now these units are in metres. You had previously declared your point
with Easting 7.4545 m and Northing 44.0 m, which is theoretically
somewhere near the equator. With geometry, you need to use all degrees
or all metres. You cannot mix units arbitrarily without transforming
them using ST_Transform.
If you are doing many things with mixed long/lat and measured metres,
you might want to take a look at the geography data type:
http://postgis.refractions.net/docs/ch04.html#PostGIS_Geography
-Mike
On 8 March 2011 04:49, michal nagy lachim1...@gmail.com wrote:
Ok,
basically I have table location(id int, point geometry(POINT)) and function
getallpointsincircle(longitude, latitude, radius) which gives set of internal
indexes from location table if point in table is in the radius of parameters
of function. Inside function I am using st_dwithin
declare
t2_row location%rowtype;
po geometry;
begin
po:=st_geomfromtext('POINT('||lon||' '||lat||')',3044);
for t2_row in select * from location
loop
if st_dwithin(t2_row.point,po, radius) is true
then return next t2_row.id;
end if;
end loop;
return;
end;
idst_astext
12
POINT(7.4545 44)
13
POINT(7.49785 44)
14
POINT(7.6845 44)
15
POINT(7.96311 44)
16
POINT(55.859755 9.847419)
these are test values in location table, as you can see point(16) has
measured coords, others are bogus.
Now, when I execute my function select
getallpointsincircle(56.162882,10.203944,45000.0), all the indexes are
returned. Approximately the distance between these places is 40 km. But when
I exchange 45000.0 with 0.46 i will get proper index(16 in this case).
2011/3/7 Mike Toews mwto...@gmail.com
You are using EPSG:3044, correct? http://spatialreference.org/ref/epsg/3044/
This projection has units of metres. ST_DWithin and related functions use
the same units of length as defined in the projection, which in this case is
metres, not degrees. You may need to elaborate with an example.
-Mike
On 7 March 2011 23:41, michal nagy lachim1...@gmail.com wrote:
Well, it sitll does not solve my problem. As I said I am using
geometry(planar) with SRID 3044, where units are degrees, therefore when
using st_dwithin will still take input in degrees.
2011/3/7 Paragon Corporation l...@pcorp.us
Use ST_DWithin instead.
http://www.postgis.org/documentation/manual-1.5SVN/ST_DWithin.html
We cover this topic in detail in the first chapter of our book which is a
free download.
http://www.postgis.us/chapter_01
Leo and Regina
From: postgis-users-boun...@postgis.refractions.net
[mailto:postgis-users-boun...@postgis.refractions.net] On Behalf Of michal
nagy
Sent: Monday, March 07, 2011 2:29 AM
To: postgis-users@postgis.refractions.net
Subject: [postgis-users] meters to degrees
Hello everyone,
I am new to Postgres and PostGIS. I have encountered one problem, that I
can not deal with.I have table with various points that have SRID=3044,
which should be UTM zone including Jutland. I have created function to
loop through all points in table and check if they are within given
radius(using geometry and function st_point_inside_circle). Now to the
problem, for some reason st_point_inside_circle takes radius in degrees,
which is very inconvenient for me. I would really need to change to
metres. If anyone has a suggestion, pls let me know.
Thank you for help
Michal
___
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] A way to split polygons?
Take a look at ST_Simplify http://postgis.refractions.net/docs/ST_Simplify.html Or ST_SimplifyPreserveTopology if you want to preserve boundaries: http://postgis.refractions.net/docs/ST_SimplifyPreserveTopology.html -Mike On 4 March 2011 09:44, Peter N. Schweitzer pschweit...@usgs.gov wrote: At http://tin.er.usgs.gov/geology/state/ and pages below it, I provide geologic map units for US states in KML for use within Google Earth. These are stored in PostgreSQL (9.0.2) with PostGIS (1.5.2). My problem is that many of the larger geologic units are represented as polygons that have a large number of vertices, often with many interior rings. Google Earth doesn't want to display polygons that have 100,000 vertices (I think the actual limit is lower, but I don't know what it is.) So my geologic maps have large irregular holes in them when viewed in Google Earth. Is there a way, using PostGIS functions, to split these polygons so that the resulting polygons have fewer vertices and thus would appear as expected in Google Earth? It seems to me one possibility would be to intersect these large polygons with Box2D's whose vertical dimensions were, say, one or two degrees. But I don't yet see how to carry this operation out. Can anyone offer suggestions as to how this might be done? Here are some of the characteristics of the data. The principal table containing the polygons is named geol_poly; the field unit_link is a relational key to other, more interesting data, and the_geom comes through shp2pgsql. select unit_link,ST_NPoints(the_geom) npt,ST_NumGeometries(the_geom) ng, ST_NumInteriorRings(the_geom) ni from geol_poly order by npt desc; unit_link | npt | ng | ni -+++-- FLwater;0 | 451759 | 1 | 8945 PAPAa;6 | 319192 | 1 | 1235 PAPAcg;6 | 216653 | 1 | 925 ALwater;0 | 205251 | 1 | 108 OHPAc;0 | 201310 | 1 | 842 OHPAm;0 | 200763 | 1 | 895 OHPAap;0 | 196422 | 1 | 1207 MNOl;0 | 188753 | 1 | 107 MNCu;0 | 165061 | 1 | 86 TNObh;6 | 152421 | 1 | 825 PAPAp;6 | 144889 | 1 | 324 MOOjc;0 | 144721 | 1 | 144 MOMk;0 | 128731 | 1 | 290 WVPAk;0 | 128402 | 1 | 934 TXKed;0 | 124666 | 1 | 720 TNMfp;10 | 122021 | 1 | 117 OHMlc;0 | 116993 | 1 | 731 TNMfp;10 | 113034 | 1 | 406 MOOr;0 | 109195 | 1 | 136 PAPAm;6 | 105906 | 1 | 395 PAPAcc;6 | 105798 | 1 | 447 WVPAm;0 | 99944 | 1 | 474 CAQ;0 | 97086 | 1 | 455 WVPAc;0 | 94277 | 1 | 455 MNOm;0 | 93054 | 1 | 53 PAPPAw;0 | 89974 | 1 | 218 TNOca;6 | 89292 | 1 | 564 WVPAa;0 | 89182 | 1 | 504 MOOr;0 | 87103 | 1 | 171 MOOg;0 | 85660 | 1 | 277 Peter -- Peter N. Schweitzer (MS 954, U.S. Geological Survey, Reston, VA 20192) (703) 648-6533 FAX: (703) 648-6252 email: pschweit...@usgs.gov http://geology.usgs.gov/peter/ ___ 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] Error Installation
Hi, Do you have the development files for PostgreSQL installed? Did the ./configure step show any warnings or errors? -Mike On 12 January 2011 04:13, mahadzar ayala mahadzar_ay...@yahoo.com wrote: Hi, I'm using Linux Suse SUSE Linux Enterprise Server 11 (ia64) 2.6.27.45-0.1-default kernel with databases version PostgreSQL 8.3. I've tried to install postgis version1.5.2 but receive an error message postg...@myhostname:~/postgis-1.5.2 make install make -C liblwgeom make[1]: Entering directory `/var/lib/pgsql/postgis-1.5.2/liblwgeom' make[1]: Nothing to be done for `all'. make[1]: Leaving directory `/var/lib/pgsql/postgis-1.5.2/liblwgeom' make -C postgis make[1]: Entering directory `/var/lib/pgsql/postgis-1.5.2/postgis' Makefile:80: /usr/local/pgsql/8.3/lib/pgxs/src/makefiles/pgxs.mk: No such file or directory make[1]: *** No rule to make target `/usr/local/pgsql/8.3/lib/pgxs/src/makefiles/pgxs.mk'. Stop. make[1]: Leaving directory `/var/lib/pgsql/postgis-1.5.2/postgis' make: *** [postgis] Error 2 Please advice me.. ___ 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] Query using ST_transform fails
I'm not sure why proj would require the NAD gridshift files for
converting a google (900913) projection to WGS84 long/lat. Do you get
any errors with this:
select st_X(wayLL), st_Y(wayLL)
from (
select ST_Transform('SRID=900913;POINT(653103 6.63036e+06 0)'::geometry,
4326) as wayLL
) as foo;
-- I get 5.86692407004312; 51.0512259090808
If there are no errors, then there are a few other things that could
be wrong. What do you get from:
select srid, proj4text
from spatial_ref_sys
where srid=900913 or srid=4326;
You can re-install proj without touching anything else. If you have
the source files still, then unzip the grid-shift files in the nad
directory, then:
make clean
./configure
make
make install
-Mike
On 4 November 2010 13:37, Torsten Mohr tm...@s.netic.de wrote:
Hello,
thanks for your help.
Regarding your questions:
No, i don't use www.pgrpms.org, my system is openSuse 11.2, i installed their
postgresql, which is version 8.4.4.
Yes, i installed Proj4 from source. I have to say i'm not sure any more if i
installed Proj-datumgrid in the /nad subdirectory of Proj 4 before
configuring, but in /usr/local/share/proj i got:
-rw-r--r-- 1 root root 694 10. Jan 2010 GL27
-rw-r--r-- 1 root root 6385 10. Jan 2010 nad.lst
-rw-r--r-- 1 root root 19501 10. Jan 2010 nad27
-rw-r--r-- 1 root root 16559 10. Jan 2010 nad83
-rw-r--r-- 1 root root 7043 10. Jan 2010 world
-rw-r--r-- 1 root root 261 10. Jan 2010 proj_def.dat
-rw-r--r-- 1 root root 551012 10. Jan 2010 epsg
-rw-r--r-- 1 root root 453436 10. Jan 2010 esri
-rw-r--r-- 1 root root 76843 10. Jan 2010 esri.extra
-rw-r--r-- 1 root root 3702 10. Jan 2010 other.extra
-rw-r--r-- 1 root root 77820 10. Jan 2010 IGNF
Would it help to re-install Proj 4 ?
Would you expect problems doing so as i installed PostGis after Proj 4 ?
Best regards,
Torsten.
Am Dienstag, 2. November 2010 22:34:05 schrieb Mike Toews:
It appears the grid shift files are missing. These are not necessarily
installed by default.
Are you using http://www.pgrpms.org/ ? Is proj-nad installed? Did you
install proj.4 from source? If so, did you get the ZIP files and put
them in the right place before configuring?
-Mike
On 2 November 2010 14:09, Torsten Mohr tm...@s.netic.de wrote:
Hello,
(i accidentially posted this on the openstreetmap mailing list, but it
belongs here:)
I once got a hint on this mailing list to use a query like this to get
the lat/lon of the world capitals:
A)
select st_X(wayLL), st_Y(wayLL), name from (select
ST_AsText(ST_Transform(way,4326)) as wayLL, name from planet_osm_point
where capital='yes') as foo limit 5;
B)
Based on that hint i used this query:
select st_X(st_transform(way,4326)), st_Y(st_transform(way,4326)), name
from planet_osm_point where place='city' and capital='yes';
That query worked fine and i did not change my system since then (that
somehow can't be true). I now get errors for both queries:
FEHLER: transform: couldn't project point (653103 6.63036e+06 0): failed
to load NAD27-83 correction file (-38)
TIP: PostGIS was unable to transform the point because either no grid
shift files were found, or the point does not lie within the range for
which the grid shift is defined. Refer to the ST_Transform() section of
the PostGIS manual for details on how to configure PostGIS to alter this
behaviour.
Could it be that due to an RPM update of PostgreSQL some scripts need to
be reinstalled? I can still generate maps using mapnik.
What do i need to do to make those queries work again?
In a second step i'd like to extract the polygons that make up the
borders of a country as lat/lon values. I wonder if this is possible by
just applying the SQL query above to the way of a polygon? Or is there
another way to get the data i want?
Thanks for any hints,
Torsten.
___
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] Query using ST_transform fails
It appears the grid shift files are missing. These are not necessarily installed by default. Are you using http://www.pgrpms.org/ ? Is proj-nad installed? Did you install proj.4 from source? If so, did you get the ZIP files and put them in the right place before configuring? -Mike On 2 November 2010 14:09, Torsten Mohr tm...@s.netic.de wrote: Hello, (i accidentially posted this on the openstreetmap mailing list, but it belongs here:) I once got a hint on this mailing list to use a query like this to get the lat/lon of the world capitals: A) select st_X(wayLL), st_Y(wayLL), name from (select ST_AsText(ST_Transform(way,4326)) as wayLL, name from planet_osm_point where capital='yes') as foo limit 5; B) Based on that hint i used this query: select st_X(st_transform(way,4326)), st_Y(st_transform(way,4326)), name from planet_osm_point where place='city' and capital='yes'; That query worked fine and i did not change my system since then (that somehow can't be true). I now get errors for both queries: FEHLER: transform: couldn't project point (653103 6.63036e+06 0): failed to load NAD27-83 correction file (-38) TIP: PostGIS was unable to transform the point because either no grid shift files were found, or the point does not lie within the range for which the grid shift is defined. Refer to the ST_Transform() section of the PostGIS manual for details on how to configure PostGIS to alter this behaviour. Could it be that due to an RPM update of PostgreSQL some scripts need to be reinstalled? I can still generate maps using mapnik. What do i need to do to make those queries work again? In a second step i'd like to extract the polygons that make up the borders of a country as lat/lon values. I wonder if this is possible by just applying the SQL query above to the way of a polygon? Or is there another way to get the data i want? Thanks for any hints, Torsten. ___ 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] Query using ST_transform fails
Also, what is the source SRID of your geometry column(s)? If you're not sure, check the geometry_columns table, the constraints used in the DDL, or try: select distinct st_srid(way) from planet_osm_point; -Mike On 2 November 2010 14:09, Torsten Mohr tm...@s.netic.de wrote: Hello, (i accidentially posted this on the openstreetmap mailing list, but it belongs here:) I once got a hint on this mailing list to use a query like this to get the lat/lon of the world capitals: A) select st_X(wayLL), st_Y(wayLL), name from (select ST_AsText(ST_Transform(way,4326)) as wayLL, name from planet_osm_point where capital='yes') as foo limit 5; B) Based on that hint i used this query: select st_X(st_transform(way,4326)), st_Y(st_transform(way,4326)), name from planet_osm_point where place='city' and capital='yes'; That query worked fine and i did not change my system since then (that somehow can't be true). I now get errors for both queries: FEHLER: transform: couldn't project point (653103 6.63036e+06 0): failed to load NAD27-83 correction file (-38) TIP: PostGIS was unable to transform the point because either no grid shift files were found, or the point does not lie within the range for which the grid shift is defined. Refer to the ST_Transform() section of the PostGIS manual for details on how to configure PostGIS to alter this behaviour. Could it be that due to an RPM update of PostgreSQL some scripts need to be reinstalled? I can still generate maps using mapnik. What do i need to do to make those queries work again? In a second step i'd like to extract the polygons that make up the borders of a country as lat/lon values. I wonder if this is possible by just applying the SQL query above to the way of a polygon? Or is there another way to get the data i want? Thanks for any hints, Torsten. ___ 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] Postgres Polygon to Postgis Geometry
Hi Wagner, I'm not sure what you mean by geo and non-geo .. you mean georeferenced, like longitude/latitude? For points, you can keep thing binary and just use ST_MakePoint(x,y). And to assign a coordinate system, use it with ST_SetSRID: SELECT ST_SetSRID(ST_MakePoint(p[0], p[1]), 4326) from ( select point '(-123.112793, 49.246293)' as p ) as foo; I'm not sure how to do similar binary conversions for path and polygon, or other types. You might have to cast to text, and wrangle it into some form of WKT, then pass it to ST_SetSRID(text, integer) to make a geometry object for PostGIS. -Mike On 26 October 2010 18:17, Wagner Santos wagner.des...@gmail.com wrote: I created a Postgis Geometry from Postgres Polygon converting them from string and using linefromtext to create a geometry but this information isn't geo. I have non geo points and I want to convert to my correct position and set SRID, for example P(70 72) to P(-54 -25), how can I do this? Thanks! On Tue, Oct 26, 2010 at 1:18 PM, Wagner Santos wagner.des...@gmail.com wrote: Hello, I'm new with postgis and I have a legacy database with Postgres Polygon, I want to convert this to a Postgis Geometry. Is there a way? I try to do with replace string and linefromtext with sucess, but I think doesn't better solution because I can't set a valid SRID. Thanks! ___ 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] Postgres Polygon to Postgis Geometry
Ah, you are looking for this:
http://postgis.refractions.net/docs/ST_Transform.html
So you need to cast your object as a polygon, set the SRID to your
projected coordinate system (I'm just guessing SRID=29181 for your
example http://spatialreference.org/ref/epsg/29181/ .. look up the
SRID for your original data). Then it can be transformed into long/lat
coords:
SELECT ST_Transform(ST_SetSRID('BOX(743295.3125 7182512.5,744414.75
7183817.5)'::box2d::geometry, 29181), 4326);
-Mike
On 26 October 2010 20:01, Wagner Santos wagner.des...@gmail.com wrote:
Mike,
Yes, I mean georeferenced (sorry for my English). I made a cast to text and
converted to postgis geometry, but I couldn't use ST_SetSRID.
I would like to tranfer a polygon to correct position. I have a
Polygon BOX(743295.3125 7182512.5,744414.75 7183817.5) and I like to
transfer it to BOX(-54.515022277832 -25.4530487060547,-54.5029907226562
-25.4447135925293).
I tried ST_Affine but I don't know the correct parameters, I tried ST_Scale
but I had the same problem. My idea was transform and set srid.
On Tue, Oct 26, 2010 at 11:44 PM, Mike Toews mwto...@gmail.com wrote:
Hi Wagner,
I'm not sure what you mean by geo and non-geo .. you mean
georeferenced, like longitude/latitude?
For points, you can keep thing binary and just use ST_MakePoint(x,y).
And to assign a coordinate system, use it with ST_SetSRID:
SELECT ST_SetSRID(ST_MakePoint(p[0], p[1]), 4326)
from (
select point '(-123.112793, 49.246293)' as p
) as foo;
I'm not sure how to do similar binary conversions for path and
polygon, or other types. You might have to cast to text, and wrangle
it into some form of WKT, then pass it to ST_SetSRID(text, integer) to
make a geometry object for PostGIS.
-Mike
On 26 October 2010 18:17, Wagner Santos wagner.des...@gmail.com wrote:
I created a Postgis Geometry from Postgres Polygon converting them from
string and using linefromtext to create a geometry but this information
isn't geo.
I have non geo points and I want to convert to my correct position and
set
SRID, for example P(70 72) to P(-54 -25), how can I do this?
Thanks!
On Tue, Oct 26, 2010 at 1:18 PM, Wagner Santos wagner.des...@gmail.com
wrote:
Hello,
I'm new with postgis and I have a legacy database with Postgres
Polygon, I
want to convert this to a Postgis Geometry. Is there a way?
I try to do with replace string and linefromtext with sucess, but I
think
doesn't better solution because I can't set a valid SRID.
Thanks!
___
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] Basic duplicating database question
On 21 October 2010 04:38, lplateandy a...@centremaps.co.uk wrote: Of course, even better would be a way to reindex whilst a database is in use but i'm under the impression that this is not possible at the moment? If you DROP/CREATE your index then reads are blocked. But if you use REINDEX then only writes are blocked, while reads are still allowed using the old index. http://www.postgresql.org/docs/9.0/static/sql-reindex.html -Mike ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
Re: [postgis-users] Basic duplicating database question
On 21 October 2010 07:24, lplateandy a...@centremaps.co.uk wrote: Hi Mike, OK - that's really useful. Does that only work for 9 or does it just happen you're pointing to the 9 document? The doc says It also takes an exclusive lock on the specific index being processed, which will block reads that attempt to use that index. Does that mean that i'm not really any better off as the spatial index is really the critical means controlling the reading of data into my GIS etc? What would be your scenario for a full vacuum? Oh right, there is a lock on reads to the index with REINDEX, so the index is useless. I missed that detail. A table without a readable index is probably no good for your situation. Here is another helpful doc page to explain locks: http://www.postgresql.org/docs/8.4/static/explicit-locking.html Also, to change the doc version, just edit the URL to your version (e.g., replace 8.4 with 8.2 or whatever). -Mike ___ 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] AddGeometryColumn BOX3D
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
On 18 October 2010 02:34, Jan Saalbach f...@dubmosphere.de wrote:
Hi all,
what is the geometry type to chose when storing BOX3D type geometries? Of
what type does the column need to be? I mean how do I phrase this correctly?
---
postgisdb=# SELECT AddGeometryColumn('boxes','box3d',31467,'BOX',3);
FEHLER: Invalid type name - valid ones are:
POINT, MULTIPOINT,
LINESTRING, MULTILINESTRING,
POLYGON, MULTIPOLYGON,
CIRCULARSTRING, COMPOUNDCURVE, MULTICURVE,
CURVEPOLYGON, MULTISURFACE,
GEOMETRY, GEOMETRYCOLLECTION,
POINTM, MULTIPOINTM,
LINESTRINGM, MULTILINESTRINGM,
POLYGONM, MULTIPOLYGONM,
CIRCULARSTRINGM, COMPOUNDCURVEM, MULTICURVEM
CURVEPOLYGONM, MULTISURFACEM,
or GEOMETRYCOLLECTIONM
---
The reason I need to do this is I would like to use the -operator on 2
boxes. And comparing the boxes from point3d-sets directly gives me a not
enough memory error.
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] pgsql2shp
As far as I know, all versions of pgsql2shp work just fine on Win7, and they should do exactly what they are documented to do for the situation you describe. What exactly are your problems? If the command had an error and didn't produce any files, what did you type? -Mike On 10 October 2010 14:03, Bob Pawley rjpaw...@shaw.ca wrote: I'm still having problems. My pgsql2shp version is - RCSID: $Id: pgsql2shp.c 4914 2009-11-27 18:12:43Z pramsey $ RELEASE: 1.4 Is this the right version to be using on Win 7?? Bob -- From: Stephen Woodbridge wood...@swoodbridge.com Sent: Sunday, October 10, 2010 11:14 AM To: postgis-users@postgis.refractions.net Subject: Re: [postgis-users] pgsql2shp On 10/10/2010 1:52 PM, Bob Pawley wrote: Hi I am using the following pgsql2shp -f Test.shp -u postgres -P London2342 -k PDW p_id.image -g the_geom to export a shape file from postgis. Even tho I specify a single geometry column to be exported I get warnings that column names have been truncated. DBF files can only have a maximum of 11 character column names. You probably have some that are longer. You can work around this problem by using a query at the end of the commandline like: 'select col1, col2, col3whichisverylong as col3, ... the_geom from p_id.image' Command prompt also informs me that No prj file will be generated Also a warning 'cannot determine spatial reference. I thought the default was -1? -1 is the default and it means that you have not defined a projection so it can not create a .prj file. It is generally a good idea to always define a projection for your data. -Steve W I also get messages- Done (postgis major version: 1) Output shape: Polyline Dumping: xx [8 rows] What am I doing wrong??? Bob ___ 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] Hello
You can try making a map visually in QGIS, then export it to MapServer using a plugin: http://spatialserver.net/qgis2ms/ The conversion isn't always 1-to-1, but it a good visual start to developing MAP files. -Mike On 1 October 2010 23:17, Nitesh Phadatare nsp@gmail.com wrote: how to use postgis on MapServer. means i want steps how to write mapfile an how to load it? also how to make visible map on MapServer -- Nitesh Phadatare, INDIA ___ 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] Geography type
You need PostGIS version 1.5.x and you can read about the geography type here: http://postgis.refractions.net/documentation/manual-1.5/ch04.html#PostGIS_Geography and see supported functions here: http://postgis.refractions.net/documentation/manual-1.5/ch08.html#PostGIS_GeographyFunctions -Mike On 23 September 2010 06:31, Gastón Lucero gaston.luc...@sitrack.com wrote: Sorry for my english. I would like to know the use of the geographic data type. Actually i have to work in represent spherical data. Thanks. Gaston Lucero ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
Re: [postgis-users] How remove a single part from a multipart geometry
There aren't any delete/remove/drop geometry functions, so you would need dissect and re-create the geometry. But, if you want to do this visually, this is easy. If you have a recent version of QGIS, just start editing and use the Delete part edit tool. -Mike On 23 September 2010 10:28, Andrea Peri 2007 aperi2...@gmail.com wrote: Hi, I need to remove a single part from a multipart geometry. For example having a multipolygon with three part I need to remove one of these parts so the result is a multipolygon with two parts. In postgis I find a function to extract a single part from a multipart ( ST_GeometryN ) but don't find nothing to remove one. So the only method I know is re-create the geometry skipping the part unwanted, but I hope to find somethink more simple. Thx, Andrea. ___ 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] GEOS pointonsurface() threw an error!
On 17 August 2010 22:02, Ricardo Bayley ricardo.bay...@gmail.com wrote: I have other 96 invalid geometries in a table of 22k records. From those 96, only 1 has the Self-intersection invalid reason. The rest of them have a Ring Self-intersection. These last dont through an error exception, only the one with the Self-intersection error does. I guess I should do propper geometry validation first. One tip I would recommend is after you clean up your geometries is to add a check constraint to ensure the geometries will always be valid: ALTER TABLE mytable ADD CONSTRAINT enforce_valid_geometry CHECK (ST_IsValid(geometry)); -Mike ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
Re: [postgis-users] st_transform, irreversible?
Hi,
Your coordinates may be flipped. Was it 59N 18E? If so, use x,y
notation: 'POINT(18 59)', which results in 'POINT(18.6
58.99905)', which is close enough.
Also keep in mind that you are outside the projection bounds:
http://spatialreference.org/ref/epsg/3021/ (just a bit too far east).
Whenever you are outside the projection bounds, the likelihood of
storage precision errors increase. To understand why this is, you can
think of taking the tangent of a two angles that are nearly a
right-angle (89.9991 and 89.9992) which have very different results
due to nature of the geometry.
-Mike
On 11 August 2010 13:10, Erik Rehn e...@slagkryssaren.com wrote:
Hello Postgis Users!
This is my first post on this list so I will start by asking
a simple (and probably stupid) question. :)
While using ST_AsKml() to produce an overlay for Google Earth I
noticed that all my geometries where shifted slightly south-east.
I figured this had something to do with the transformation between
the projection that my geometries are stored in (SRID 3021) and WGS84 (4326)
that is outputted by ST_AsKml()
Just to test I ran this:
SELECT ST_AsText(
ST_Transform(
ST_Transform(
ST_GeomFromText('POINT(59 18)',4326),
3021),
4326));
I input a point in WGS84 (59,18), transforms it to 3021 and then back to
WGS84. The result I get is:
POINT(58.8672757036296 18.0394763349359)
Can anyone explain this? Am I missing something regarding ST_Transform()?
Im running Postgis 1.5 on Windows.
Thank you for any help!
/Erik
--
Erik Rehn
Slagkryssaren
e...@slagkryssaren.com
www.slagkryssaren.com
___
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] postgres data tables package (ORACLE Forms replacement?)
Hi John, For the desktop, the best I've seen is MS Access, but it is certainly not FOSS. It is well used, documented, supported, easy form development, etc. A runner up is OpenOffice.org, but I haven't been able to develop too much on it, because I run into odd GUI behaviours that are deal-breakers (e.g., the scroll-wheel of the mouse can accentually modify numeric data if it hovers over a numeric field ... although this is in the process of being fixed). Kexi (from KOffice) is yet another app, but I haven't looked at it in a few years and I don't know if it is available for Windows anymore. For the web, there is WaveMaker, which is very web 2.0 with lots of JavaScript to make it appear like a desktop application. I've only had limited work with it, so I can't say too much about it. (I recall having a poor impression due to problems with bad permission assumptions and some other data type issues). There are others out there, but I'm interested to see what other people use. -Mike On 5 August 2010 06:53, John Callahan john.calla...@udel.edu wrote: Brief background: We're currently using Oracle for our main database. Nothing too big, 15 - 20 tables and the largest table has about 100K records. We use Oracle Forms as a web-based option for inserting and updating records. I am using Postgres/PostGIS for a couple of other projects. I would like to use Postgres for our main database as well. However, I would need to come up with a solution for inserting/updating the data tables within our group (a replacement for Oracle Forms). Preferably, a web-based solution (PHP, Python, etc...) but it could be a Windows desktop solution installed on each staff members machine. Something that maintains the data integrity (e.g., forcing users to enter a date when Date is required, etc...) Does anyone know of a FOSS package that can be used to manage data tables within a Postgres database? Updating data records is the highest priority since the built-in Admin tool can be used for managing users, creating new tables, etc..., and will be done only by a database manager. Thanks for any advice or guidance you can provide. - John ___ 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 get geometry enabled Tables form Postgresql/postgis
You can either assume that they are all listed in geometry_columns or
geography_columns. Or you can do a more complicated query to include other
tables than in geometry/geography, (this is from QGIS code[1]):
select f_table_name, f_table_schema, upper(type), pg_class.relkind
from geometry_columns, pg_class, pg_namespace
where relname=f_table_name
and f_table_schema=nspname
and pg_namespace.oid=pg_class.relnamespace
and has_schema_privilege(pg_namespace.nspname,'usage')
and
has_table_privilege(''||pg_namespace.nspname||'.'||pg_class.relname||'','select')
order by f_table_schema,f_table_name;
To search geography, replace geometry_columns with geography_columns.
Geography column support is new in QGIS, and is only in trunk at the moment.
[1]
https://trac.osgeo.org/qgis/browser/trunk/qgis/src/app/postgres/qgspgsourceselect.cpp?rev=13922#L632
-Mike
On 29 July 2010 05:57, venkat ven.tammin...@gmail.com wrote:
Dear All,
How can i retrieve only spatial enabled tables form the
database(Postgresql/PostGIS).Please let me know.
I am waiting for your great response.
Thanks and Regards,
Venkat
___
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 get geometry enabled Tables form Postgresql/postgis
Oh, sorry, the search that looks for tables not listed in
geometry_columns/geography_columns is further down in the QGIS source:
https://trac.osgeo.org/qgis/browser/trunk/qgis/src/app/postgres/qgspgsourceselect.cpp?rev=13922#L714
https://trac.osgeo.org/qgis/browser/trunk/qgis/src/app/postgres/qgspgsourceselect.cpp?rev=13922#L714
-Mike
On 29 July 2010 06:34, Mike Toews mwto...@gmail.com wrote:
You can either assume that they are all listed in geometry_columns or
geography_columns. Or you can do a more complicated query to include other
tables than in geometry/geography, (this is from QGIS code[1]):
select f_table_name, f_table_schema, upper(type), pg_class.relkind
from geometry_columns, pg_class, pg_namespace
where relname=f_table_name
and f_table_schema=nspname
and pg_namespace.oid=pg_class.relnamespace
and has_schema_privilege(pg_namespace.nspname,'usage')
and
has_table_privilege(''||pg_namespace.nspname||'.'||pg_class.relname||'','select')
order by f_table_schema,f_table_name;
To search geography, replace geometry_columns with geography_columns.
Geography column support is new in QGIS, and is only in trunk at the moment.
[1]
https://trac.osgeo.org/qgis/browser/trunk/qgis/src/app/postgres/qgspgsourceselect.cpp?rev=13922#L632
-Mike
On 29 July 2010 05:57, venkat ven.tammin...@gmail.com wrote:
Dear All,
How can i retrieve only spatial enabled tables form the
database(Postgresql/PostGIS).Please let me know.
I am waiting for your great response.
Thanks and Regards,
Venkat
___
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 import SHP without superuser permission?
Is your user account the owner of geometry_columns? (often this is postgres) If not, then use the commands to allow permissions: GRANT ALL ON TABLE geometry_columns TO someuser; -- And while you are at it: GRANT ALL ON TABLE spatial_ref_sys TO someuser; If you have multiple users, you might want to consider using roles that are inherited by other users. This way you only need to enable the role permissions to the tables once, then each user can inherit that role. -Mike On 28 July 2010 00:56, kreshna_icehe...@yahoo.com kreshna_icehe...@yahoo.com wrote: I tried to import SHP files into postGIS. As usual, I use shp2pgsql to create the SQL script, and then I use psql to execute the script. The postgreSQL user acccount I use with psql is the owner of the target database. However, it is not superuser. When I run psql to execute the script, I got the following error: ERROR: permission denied for relation geometry_columns I eventually decided to give superuser permission to the user account. After I gave superuser permission, the script ran without problem. Question: why did I got permission denied error? The user account is already the owner of the target database; why can't it import the data. Is there a way to import SHP files into postGIS database without superuser permission? Thanks, -Kresh ___ 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] PostGIS to SpatiaLite
Hi all, I need to copy several PostGIS tables to a SpatiaLite file, so it can be used on field laptops. A similar question appeared a year ago[1], but there were no solutions. I understand that ogr2ogr has support for SpatiaLite using GDAL 1.7.x, however I cannot seem to configure the GDAL source to enable SpatiaLite[2]. Yet another option is FME, but I don't have time/budget for this. FDO doe Are there any other freely available server-side tools to do the conversion? Thanks. -Mike [1] http://postgis.refractions.net/pipermail/postgis-users/2009-June/023707.html [2] http://lists.osgeo.org/pipermail/gdal-dev/2010-July/025448.html ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
Re: [postgis-users] PostGIS to SpatiaLite
Ok ok, I've finally tamed ogr2ogr to a working state. When configuring GDAL, use --with-spatialite=/usr/local (for a locally installed SpatiaLite library), then make and install GDAL (note my build attempt with Ubuntu 10.04 was unsuccessful for reasons I don't have time to care for, but my build with 9.04 works). To create the SpatiaLite file export the first layer: $ ogr2ogr -f SQLite -dsco SPATIALITE=yes myfile.sqlite PG:dbname=mydb user=myuser password=mypass myschema.mytable And for additional layer/tables: $ ogr2ogr -f SQLite -update myfile.db PG:dbname=mydb user=myuser password=mypass myschema.my2ndtable The resulting file work with viewers, including SpatiaLite GUI/GIS, Quantum GIS. I've started http://trac.osgeo.org/postgis/wiki/SpatiaLite -Mike On 24 July 2010 10:43, Mike Toews mwto...@gmail.com wrote: Hi all, I need to copy several PostGIS tables to a SpatiaLite file, so it can be used on field laptops. A similar question appeared a year ago[1], but there were no solutions. I understand that ogr2ogr has support for SpatiaLite using GDAL 1.7.x, however I cannot seem to configure the GDAL source to enable SpatiaLite[2]. Yet another option is FME, but I don't have time/budget for this. FDO doe Are there any other freely available server-side tools to do the conversion? Thanks. -Mike [1] http://postgis.refractions.net/pipermail/postgis-users/2009-June/023707.html [2] http://lists.osgeo.org/pipermail/gdal-dev/2010-July/025448.html ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
Re: [postgis-users] Record permission
You after something called row level permissions. Search that term and see some examples on how to implement it. It is possible, but it is difficult to set up and maintain. PostgreSQL does not support it out of the box. Basically, you need to have a table of users, which mirrors your pg_user system view, and the PostGIS table(s) that require row level permissions are accessed using views with rules. The rules need to be tuned to understand the permissions from your user table using CURRENT_USER. I did this once as a proof of concept, but not in production use. -Mike On 23 July 2010 00:10, Jaime Fiers jaime.fi...@gmail.com wrote: Hi again guys. Another question. Is it possible to get record/line level permission. I mean, I need some user can view only records that math with some condition inside a table... another user can edit some different recods... I found web information, but I was no able to get it. Thanks in advance ___ 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] making a line from a known point and direction
Except that you need radians .. not degrees, so multiply your degrees by pi()/180 to convert to radians. See this similar discussion: http://postgis.refractions.net/pipermail/postgis-users/2010-January/025752.html -Mike On 20 July 2010 04:57, Fred Lehodey leho...@gmail.com wrote: Hi ahmet, you can use something like that (and easily write a function): here, the point is (0 0) : ST_makepoint(0,0) ,azimuth is 45 and distance is 50. select st_makeline(st_makepoint(0,0),st_makepoint(xx,yy)) as the_geom FROM ( select ST_x(st_makepoint(0,0)) + (50 * (sin(45))) as xx, ST_y(st_makepoint(0,0)) + (50 * (cos(45))) as yy ) as foo Fred. On Tue, Jul 20, 2010 at 12:26 PM, ahmet temiz ahmettemi...@gmail.com wrote: hello I want to make a linestring from a known point and known direction (as azimuth) with certain length. how can I do that ? regards ___ 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] Multipoint thresold
What tells you there is nothing in your multipoint geometry? Client software can be buggy with large unexpected numbers, so the error may be with whatever tool you are using. Can you verify the number of points using SQL? -Mike On 2 July 2010 08:49, Biddy newska...@riomhphost.net wrote: Hi, thanks for your reply. Actually, I do use sql in order to query my data. It tells me that there is nothing in my multipoint geometry. B. Zitat von Nicolas Ribot nicolas.ri...@gmail.com: Hi everyone, I am using multipoints and while filling them I noticed that there appears to be a thresold between 1,100 and 1,200 points per multipoint (no error, everything loads fine, just the multipoint geometry columns are empty). I haven't found any documentation on this. So, I am wondering whether this is a general size restriction or if it depends on the computer you're on as well. Hi No size restriction. Is it possible you look at your data using pgAdmin ? If it is the case, pgAdmin is limiting the number of characters when displaying tables. try a select st_numGeometries(geom) to check all the points are added to the multi object. Or use psql to see the data. Nicolas ___ 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] whats the different between ST_Extent() and ST_Estimated_Extent()
ST_Estimated_Extent for large tables can be faster, since it uses
cached statistics from the last VACUUM ANALYZE. The time difference is
not evident with only a few rows of data.
Also, ST_Estimated_Extent only works on tables, and not VIEWs (an
error is returned). I had to overwrite the ST_Estimated_Extent
function to use ST_Extent on one server that uses the AutoCAD Map 3D
FDO PostGIS Provider, since it had the ST_Estimated_Extent command
hard-wired in the layer-load for both views and tables (resulting in
instability AutoCAD).
-Mike
On 27 June 2010 23:32, nguyen liem liemnguye...@gmail.com wrote:
Hi All,
I have 2 queries:
SELECT ST_Extent(point) FROM schema.mytable;
SELECT ST_Estimated_Extent('schema', 'mytable', 'point');
2 queries above were returned for me same result (BOX2D):
BOX(11.1806726455688 55.3748893737793,24.1317653656006 67.8551025390625)
I don't know what is the different between 2 functions, should I use
ST_Extent or ST_Estimated_Extent in this case.
And ... Will be better, if you explain me when we need to use ST_Extent and
when we need to use ST_Estimated_Extent ;)
Cheers.
--
Liem Nguyen
Developer
Axon Active Corp.
Direct Line : +84 38 948 4645
Fax : +84 38 811 3489
Mobile Phone : +84 168 994 8897
Skype ID : liemnguyendl2204
Email : liem.ngu...@axonactive.net / liemnguye...@gmail.com
Web Site : http://axonactive.vn
Address : 87 Thang Long Street,Gate 2 Ward 4,
Tan Binh District, HCM city, Vietnam.
___
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] Converting a LineString to MultiPoint
If you have PostGIS 1.5, then you can use ST_DumpPoints: http://postgis.refractions.net/docs/ST_DumpPoints.html E.g.: SELECT gid, ST_AsText(ST_Collect(ST_AsText(geom))) FROM ( SELECT gid, (ST_DumpPoints(g.geom)).* FROM (SELECT 1 as gid, 'LINESTRING (0 0, 0 3, 3 4)'::geometry AS geom UNION ALL SELECT 2 as gid, 'LINESTRING (1 1, 2 6, 7 7)'::geometry AS geom) AS g ) j GROUP BY gid; -Mike On 17 June 2010 14:57, Andrea Peri 2007 aperi2...@gmail.com wrote: Hi, I have a table of LineStrings, and need to create a table of MultiPoints, where every multipoint is using the same vertex of a LineString. There is a method to do this using only sql ? Thx, Andrea. ___ 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] Converting a LineString to MultiPoint
Or, if you have version 1.5, you could use a simple WKT hack to text replace 'LINESTRING' with 'MULTIPOINT', since the rest is the same: SELECT gid, ST_AsText(replace(ST_AsEWKT(geom), 'LINESTRING', 'MULTIPOINT')::geometry) FROM (SELECT 1 as gid, 'LINESTRING (0 0, 0 3, 3 4)'::geometry AS geom UNION ALL SELECT 2 as gid, 'LINESTRING (1 1, 2 6, 7 7)'::geometry AS geom) AS g; Again, this is a complete hack, and assumes you have all LINESTRING types. I use ST_AsEWKT in case you have more than 3 dimensions and/or an SRID. -Mike On 17 June 2010 16:36, Mike Toews mwto...@gmail.com wrote: If you have PostGIS 1.5, then you can use ST_DumpPoints: http://postgis.refractions.net/docs/ST_DumpPoints.html E.g.: SELECT gid, ST_AsText(ST_Collect(ST_AsText(geom))) FROM ( SELECT gid, (ST_DumpPoints(g.geom)).* FROM (SELECT 1 as gid, 'LINESTRING (0 0, 0 3, 3 4)'::geometry AS geom UNION ALL SELECT 2 as gid, 'LINESTRING (1 1, 2 6, 7 7)'::geometry AS geom) AS g ) j GROUP BY gid; -Mike On 17 June 2010 14:57, Andrea Peri 2007 aperi2...@gmail.com wrote: Hi, I have a table of LineStrings, and need to create a table of MultiPoints, where every multipoint is using the same vertex of a LineString. There is a method to do this using only sql ? Thx, Andrea. ___ 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] Using grids transformation method with PostGis
The problem is probably with the grid files and proj.4. Is there any way to download the GSB files for testing? You can test with cs2cs to see if the proj4 string works or not. For example, with Canada's NTv2 grid file, I can test to see if I installed it correctly: cs2cs -v +proj=latlong +ellps=clrk66 +nadgri...@ntv2_0.gsb +to +proj=latlong +ellps=GRS80 +datum=NAD83 then type: -111 50, and I should see output with different (and correct) coordinaets. If you see the same coordinates, then either the coordinates are outside the grid, or the grid file is not found. (I'd love to see some exceptions thrown somewhere in the code). If the test works for cs2cs, then it will work for PostGIS by updating the spatial_ref_sys table with the projection info. -Mike 2010/6/15 Luís de Sousa luis.a.de.so...@gmail.com Hello everyone, I have tried to sort this issue in several ways, checking the correct projection settings, trying other installations of PostGis but the problem remains: PostGis seems unable to use the proj files for the grid transformation method. Is this a bug that should be reported? How can I do so? Thank you, Luís ___ 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] Swap Z and M in a geometry with three dimensions
Hi, I have a geometry with three dimensions that was incorrectly encoded as LINESTRINGM, but it is a spatial 3D object. How do I swap out the M coordinate and use it for the Z coordinate? Is there a simple way? Thanks. -Mike ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
