Re: [postgis-users] refractions web site
El Tuesday 21 September 2010, Guillaume Sueur escribió: Hi, What's wrong with www.refractions.net website and its sub-domains like postgis.refractions.net ? Has it moved somewhere else ? Did they forget to pay the domain? -- María Arias de Reyna Domínguez Área de Operaciones Emergya Consultoría Tfno: +34 954 51 75 77 / +34 607 43 74 27 Fax: +34 954 51 64 73 www.emergya.es ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
[postgis-users] combine serveral parts of the country
Hi Ralf,
yes the second dataset was imported with -append.
I didn't explain well:
Ogr2ogr creates the ogc_fid by incrementing, there aren't double ogc_fid s.
But the Interlis himself tells ogr2ogr to create the linking attributes and
fill them with the numbers written in the ITF-File.
('Parcelle_von' is in the right table and '_tid' in the left table). And
it's here, where I have the collision when I'm importing the second
dataset... it's normal, because
The ITF-Files were written be different persons. They re-used the same
links...
Gr
Thomas, aus dem Wallis
-Ursprüngliche Nachricht-
Von: Ralf Suhr [mailto:ralf.s...@itc-halle.de]
Gesendet: Mittwoch, 22. September 2010 11:28
An: PostGIS Users Discussion
Cc: Thomas Andres
Betreff: Re: [postgis-users] combine serveral parts of the country
Hi Thomas,
ogc_fid can't exists twice. It is the primary key from ogr2ogr. Did you
import
your secound dataset with ogr2ogr -append?
Gr
Ralf
Am Mittwoch 22 September 2010 09:15:49 schrieb Thomas Andres:
Salü Ralf,
Thank you for your answer.
-When I use ogc_fid as primary key, I cant import a second village,
because the IDs of their features were created by different offices,
different applications, so its possible that the same feature-id exists
twice.
-When I use shemas, I have to rewrite all SQL Querys. Select blabla
from village1.parcelles Union all select blabla from village2.parcelles
Union all etc
This can become very long J
If its possible, I would like that postgres sees, that the feature-id
which ogr2ogr is importing now, already exists,
so give to the new
feature a new feature-id, and at the same time he checks all foreign keys
and adjust them to the new feature-id to maintain the link. (J
complicated)
Is there a easy solution?
Thank you for your help.
Salutation from Chuchichäschtli
P.S.: Chuchichäschtli is a box in the kitchen
--
Hi Chuchichäschtli,
you can use ogc_fid as primary key from your tables or create on schema
for
one village.
What Chuchichäschtli mean in german?
Gr
Ralf
Am Montag 20 September 2010 09:34:11 schrieb Thomas Andres:
Dear List,
im Swiss and my English isnt the most best. Even, I will try the
explain
You my problem. Perhaps You will understand J
In Switzerland the parts of the country were measured by different
offices.
The informations of the terrain (parcelles, rows, lakes, jungle etc
) are
exchanged by Interlis Files (*.itf).
With ogr2ogr this files can be imported into a Postgres Database.
When You import only one file, its not a problem. Every Line, Point,
Polygone and so on, has its own Feature-Id, which can be linked with
another feature.
I.e.: The geometry of a parcelle is linked with his number.
Now, when I would like add a second village to my GIS, in the same
Database, to be loaded in Mapserver with a mapfile layer
the parcelle
with the same Feature ID will appear twice
so the link to the number of
the parcelle is wrong
What I made in a first time: after have imported the first village, I
add
to the primary and foreign keys, which I really Im going to use in my
GIS
a Char (a in example)
by using PostgreSQL
only after I imported the
second file. This is a solution which works, but not for long time J
I asked me, or better: I ask You, if there isnt a mechanism in PostGIS
or
PostgreSQL which is specially implemented for this kind of collage
Some
kind of namespaces or
I dont know
You understand =-) ?
Thänk You very much for Your help.
Salutation from Chuchichäschtli
___
postgis-users mailing list
postgis-users@postgis.refractions.net
http://postgis.refractions.net/mailman/listinfo/postgis-users
Re: [postgis-users] combine serveral parts of the country
Hi Ralf,
the solution with the views is interesting.
Thank you very much
-Ursprüngliche Nachricht-
Von: Ralf Suhr [mailto:ralf.s...@itc-halle.de]
Gesendet: Mittwoch, 22. September 2010 14:17
An: Thomas Andres
Betreff: Re: [postgis-users] combine serveral parts of the country
Hi Thomas,
now I know what you mean. ogr2ogr create more then one table per import.
You can import dataset in a temporary schema, look for the highest value for
Parcelle_von, update the temporary tables (Parcelle_von, _tid) and copy
temorary data to the main tables. This will only work for importing data not
for updating katastral data.
The other way is to use seperat schemas, the same sequence
'parcels_ogc_fid_seq' for all parcel tables and a view like
Create View parcels AS
Select * FROM kanton1.parcels
UNION ALL
Select * FROM kanton2.parcels
;
Gr
Ralf
Am Mittwoch 22 September 2010 13:33:20 schrieben Sie:
Hi Ralf,
yes the second dataset was imported with -append.
I didn't explain well:
Ogr2ogr creates the ogc_fid by incrementing, there aren't double ogc_fid
s.
But the Interlis himself tells ogr2ogr to create the linking attributes
and
fill them with the numbers written in the ITF-File.
('Parcelle_von' is in the right table and '_tid' in the left table). And
it's here, where I have the collision when I'm importing the second
dataset... it's normal, because
The ITF-Files were written be different persons. They re-used the same
links...
Gr
Thomas, aus dem Wallis
-Ursprüngliche Nachricht-
Von: Ralf Suhr [mailto:ralf.s...@itc-halle.de]
Gesendet: Mittwoch, 22. September 2010 11:28
An: PostGIS Users Discussion
Cc: Thomas Andres
Betreff: Re: [postgis-users] combine serveral parts of the country
Hi Thomas,
ogc_fid can't exists twice. It is the primary key from ogr2ogr. Did you
import
your secound dataset with ogr2ogr -append?
Gr
Ralf
Am Mittwoch 22 September 2010 09:15:49 schrieb Thomas Andres:
Salü Ralf,
Thank you for your answer.
-When I use ogc_fid as primary key, I cant import a second village,
because the IDs of their features were created by different offices,
different applications, so its possible that the same feature-id exists
twice.
-When I use shemas, I have to rewrite all SQL Querys. Select blabla
from village1.parcelles Union all select blabla from village2.parcelles
Union all etc
This can become very long J
If its possible, I would like that postgres sees, that the feature-id
which ogr2ogr is importing now, already exists,
so give to the new
feature a new feature-id, and at the same time he checks all foreign
keys and adjust them to the new feature-id to maintain the link. (J
complicated)
Is there a easy solution?
Thank you for your help.
Salutation from Chuchichäschtli
P.S.: Chuchichäschtli is a box in the kitchen
--
Hi Chuchichäschtli,
you can use ogc_fid as primary key from your tables or create on schema
for
one village.
What Chuchichäschtli mean in german?
Gr
Ralf
Am Montag 20 September 2010 09:34:11 schrieb Thomas Andres:
Dear List,
im Swiss and my English isnt the most best. Even, I will try the
explain
You my problem. Perhaps You will understand J
In Switzerland the parts of the country were measured by different
offices.
The informations of the terrain (parcelles, rows, lakes, jungle etc
) are
exchanged by Interlis Files (*.itf).
With ogr2ogr this files can be imported into a Postgres Database.
When You import only one file, its not a problem. Every Line, Point,
Polygone and so on, has its own Feature-Id, which can be linked with
another feature.
I.e.: The geometry of a parcelle is linked with his number.
Now, when I would like add a second village to my GIS, in the same
Database, to be loaded in Mapserver with a mapfile layer
the parcelle
with the same Feature ID will appear twice
so the link to the number of
the parcelle is wrong
What I made in a first time: after have imported the first village, I
add
to the primary and foreign keys, which I really Im going to use in
my
GIS
a Char (a in example)
by using PostgreSQL
only after I imported the
second file. This is a solution which works, but not for long time J
I asked me, or better: I ask You, if there isnt a mechanism in
PostGIS
or
PostgreSQL which is specially implemented for this kind of collage
Some
kind of namespaces or
I dont know
You understand =-) ?
Thänk You very much for Your help.
Salutation from Chuchichäschtli
Re: [postgis-users] Testing on Windows PostGIS 1RC1
Nicklas, I will try to do some testing this evening. I tried yesterday on windows but had problems with compiling postgresql 9.0I don't remember the error message now. I will try on another box. Regina has there been any special things compiling 9.0 in mingw for you? I have not updated anything (have just been glad it has worked :-) )so maybe I should just reinstall mingw and everything. /Nicklas We do use some hacks to compile against PostgreSQL 9.0 on windows that we don't need to do for prior versions. That's why I said -- maybe the issues I have on Windows are not really a good measure of what others will experience and someone needs to test on Linux or Mac. 1) can't get it to install without this: sed 's,$(PERL),perl,g' postgis/Makefile postgis/Makefile2 mv postgis/Makefile2 postgis/Makefile Which I documented in the PostGIS 1.5 section of http://trac.osgeo.org/postgis/wiki/UsersWikiWinCompile 2) For some reason we can't do a make check against the MingW build of PostgreSQL 9.0. It always gives this error createdb: could not connect to database postgres: FATAL: parameter port cannot be changed without restarting the server (could be because we have so many versions of PostgreSQL, but the other versions of PostgreSQL don't have this issue) So to get around this mess -- after make install on MingW, our script copies the files to our Windows PostgreSQL 9.0 install and then does the make check For the others -- make check against the mingW builds works fine and all checks for 8.3 and 8.4 against mingW builds pass with flying colors. -- Mark to answer your question about the diffs in the PostgreSQL 9.0 -- they look like below - so it seems to me that the PostgreSQL 9.0 (and it could be some default sent in the Windows build just providing the count of records (more information) than what the regress is expecting. *** wmsservers_expected Tue Nov 17 12:23:16 2009 --- /tmp/pgis_reg_124/test_46_out Mon Sep 20 08:54:34 2010 *** *** 1,6 Starting up MapServer/Geoserver tests... Setting up the data table... ! SELECT NOTICE: ALTER TABLE / ADD PRIMARY KEY will create implicit index wmstest_pkey for table wmstest ALTER TABLE Running Geoserver 2.0 NG tests... --- 1,6 Starting up MapServer/Geoserver tests... Setting up the data table... ! SELECT 2343 NOTICE: ALTER TABLE / ADD PRIMARY KEY will create implicit index wmstest_pkey for table wmstest ALTER TABLE Running Geoserver 2.0 NG tests... *** tickets_expectedMon Feb 8 14:19:23 2010 --- /tmp/pgis_reg_124/test_47_out Mon Sep 20 08:54:36 2010 *** *** 45,51 #210b| #213|17 #234|COMPOUNDCURVE((0 0,1 1)) ! SELECT #241|0 #254|010700 #259| --- 45,51 #210b| #213|17 #234|COMPOUNDCURVE((0 0,1 1)) ! SELECT 1 #241|0 #254|010700 #259| Thanks, Regina Remember that because postgis_comments.sql has extra dependencies for the documentation build that it needs to be installed from the documentation Makefile. This is now under PGXS control too, so all you need to do is: cd doc/ make comments-install ...and you're golden. For this then we probably should put a note somewhere in the docs that if they aren't doing a make install of comments, then the comments will have to be manually copied. The point why we package the postgis_comments.sql in the tar is so that people don't need the extra xsltproc dependency and don't need to run the step you describe above. So yah I know the step above works but that is not the point. I'll have to get back to you other issue as I don't have my difffs around. All I recall is the output was something like SELECT 3456 And the regress just had SELECT But lets see what others say. We are on windows so who cares since things don't quite work the same for us anyway. Thanks, Regina ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
[postgis-users] Difference between ST_Intersects and operator
Hi all. I am working on a huge vector grid project where i need to intersect country polygon data and vector grid cells. For this i need to first select the grid cells which intersects with the country polygons. SELECT DISTINCT testgrid.gid, xcoord, ycoord, cell INTO testgrid2 FROM testgrid, cshaperef WHERE ST_Intersects(cshaperef.the_geom, testgrid.cell)=true I tried both the ST_Intersects and the operator, but i cannot really understand the difference. After looking at my results the ST_Intersects gave exactly the cells which intersected with the country polygons, while the gave many additional cells. Another interesting observation is the the is way faster than the ST_Intersects which in my case 188 polygons and 212000 cells takes a lot of time. Could anyone please elaborate on the difference? ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
[postgis-users] (no subject)
___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
[postgis-users] PostGIS windows 1.5.2 RC1 binaries
In case anyone on windows is interested in testing the PostGIS 1.5.2 RC1 We have windows 32-bit binaries posted for PostgreSQL 8.3,8.4, and 9.0 http://www.postgis.org/download/windows/experimental.php Thanks, Leo and Regina http://www.postgis.us ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
Re: [postgis-users] Difference between ST_Intersects and operator
does a bounding box intersects check where as ST_Intersects does a more intensive actual geometry intersect check. Which is why its slower. _ From: postgis-users-boun...@postgis.refractions.net [mailto:postgis-users-boun...@postgis.refractions.net] On Behalf Of Andreas Forø Tollefsen Sent: Wednesday, September 22, 2010 11:00 AM To: PostGIS Users Discussion Subject: [postgis-users] Difference between ST_Intersects and operator Hi all. I am working on a huge vector grid project where i need to intersect country polygon data and vector grid cells. For this i need to first select the grid cells which intersects with the country polygons. SELECT DISTINCT testgrid.gid, xcoord, ycoord, cell INTO testgrid2 FROM testgrid, cshaperef WHERE ST_Intersects(cshaperef.the_geom, testgrid.cell)=true I tried both the ST_Intersects and the operator, but i cannot really understand the difference. After looking at my results the ST_Intersects gave exactly the cells which intersected with the country polygons, while the gave many additional cells. Another interesting observation is the the is way faster than the ST_Intersects which in my case 188 polygons and 212000 cells takes a lot of time. Could anyone please elaborate on the difference? ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
Re: [postgis-users] Postgis to Text File
Hi, The ST_AsText function is useful in this regards for converting a geometry into Well Known Text. So SELECT ST_AsText(the_geom) from my table; will return the geometry information as strings. The inverse is accomplished with ST_GeomAsText hth Henri From: Bob Pawley rjpaw...@shaw.ca To: postgis-users@postgis.refractions.net Sent: Wed, September 22, 2010 6:01:14 PM Subject: [postgis-users] Postgis to Text File Hi I have forgotten how to extract postgis information to a text file and haven't found any info on it. Any help would be appreciated. Bob___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
Re: [postgis-users] srid for epsg 26915
David, spatialreference.org uses a local SRID with a prepended '9' while maintaining an auth_srid to match EPSG so as not to step on the toes of the SRIDs that are already in the spatial_ref_sys table. While a bit ugly, this is the intended behavior. It uses the '9' because there are no EPSG codes that begin with a 9 so it is a clear namespace to not overwrite an EPSG code regardless of what local SRID identifier is given at spatialreference.org. Clear as mud? David On Wed, Sep 22, 2010 at 11:41 AM, David Fawcett david.fawc...@gmail.comwrote: A minor issue here, but I am looking for some clarification. I am seeking the correct PostGIS srid for UTM Z15N NAD83. If I query spatialreference.org, (http://spatialreference.org/ref/epsg/26915/postgis/) I get: INSERT into spatial_ref_sys (srid, auth_name, auth_srid, proj4text, srtext) values ( 926915, 'epsg', 26915, '+proj=utm +zone=15 +ellps=GRS80 +datum=NAD83 +units=m +no_defs ', 'PROJCS[NAD83 / UTM zone 15N,GEOGCS[NAD83,DATUM[North_American_Datum_1983,SPHEROID[GRS 1980,6378137,298.257222101,AUTHORITY[EPSG,7019]],AUTHORITY[EPSG,6269]],PRIMEM[Greenwich,0,AUTHORITY[EPSG,8901]],UNIT[degree,0.01745329251994328,AUTHORITY[EPSG,9122]],AUTHORITY[EPSG,4269]],UNIT[metre,1,AUTHORITY[EPSG,9001]],PROJECTION[Transverse_Mercator],PARAMETER[latitude_of_origin,0],PARAMETER[central_meridian,-93],PARAMETER[scale_factor,0.9996],PARAMETER[false_easting,50],PARAMETER[false_northing,0],AUTHORITY[EPSG,26915],AXIS[Easting,EAST],AXIS[Northing,NORTH]]'); From a fairly recent of PostGIS, SELECT srid, auth_name, auth_srid FROM public.spatial_ref_sys WHERE auth_srid = 26915 I get: 26915 | EPSG | 26915 I am assuming that the definition at spatial ref.org is wrong? David. ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users -- David William Bitner ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
Re: [postgis-users] srid for epsg 26915
OK, that makes sense. So, it works well when installing additional SRS' , you won't mess up your stock spatial_ref_sys. The use case that I was going after was where a co-worker wanted to know what srid to use when importing data from a shapefile. I was attempting to use spatialreference.org as a reference to confirm what the standard SRID for this SRS in PostGIS was. I guess that this conflict comes up for SRS' that are part of the stock install. Maybe there is a need for another line on the spatialreference.org page for 'PostGIS SRID' ? Of course, if one already has rights to add data to the db, you can just run a simple (if you know the EPSG code): SELECT * FROM public.spatial_ref_sys WHERE auth_name = 'EPSG' AND auth_srid = 26915; David. On Wed, Sep 22, 2010 at 12:19 PM, David William Bitner bit...@gyttja.org wrote: David, spatialreference.org uses a local SRID with a prepended '9' while maintaining an auth_srid to match EPSG so as not to step on the toes of the SRIDs that are already in the spatial_ref_sys table. While a bit ugly, this is the intended behavior. It uses the '9' because there are no EPSG codes that begin with a 9 so it is a clear namespace to not overwrite an EPSG code regardless of what local SRID identifier is given at spatialreference.org. Clear as mud? David On Wed, Sep 22, 2010 at 11:41 AM, David Fawcett david.fawc...@gmail.com wrote: A minor issue here, but I am looking for some clarification. I am seeking the correct PostGIS srid for UTM Z15N NAD83. If I query spatialreference.org, (http://spatialreference.org/ref/epsg/26915/postgis/) I get: INSERT into spatial_ref_sys (srid, auth_name, auth_srid, proj4text, srtext) values ( 926915, 'epsg', 26915, '+proj=utm +zone=15 +ellps=GRS80 +datum=NAD83 +units=m +no_defs ', 'PROJCS[NAD83 / UTM zone 15N,GEOGCS[NAD83,DATUM[North_American_Datum_1983,SPHEROID[GRS 1980,6378137,298.257222101,AUTHORITY[EPSG,7019]],AUTHORITY[EPSG,6269]],PRIMEM[Greenwich,0,AUTHORITY[EPSG,8901]],UNIT[degree,0.01745329251994328,AUTHORITY[EPSG,9122]],AUTHORITY[EPSG,4269]],UNIT[metre,1,AUTHORITY[EPSG,9001]],PROJECTION[Transverse_Mercator],PARAMETER[latitude_of_origin,0],PARAMETER[central_meridian,-93],PARAMETER[scale_factor,0.9996],PARAMETER[false_easting,50],PARAMETER[false_northing,0],AUTHORITY[EPSG,26915],AXIS[Easting,EAST],AXIS[Northing,NORTH]]'); From a fairly recent of PostGIS, SELECT srid, auth_name, auth_srid FROM public.spatial_ref_sys WHERE auth_srid = 26915 I get: 26915 | EPSG | 26915 I am assuming that the definition at spatial ref.org is wrong? David. ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users -- David William Bitner ___ 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] srid for epsg 26915
The stock SRID in PostGIS for items from the EPSG database is always going to be the same as the EPSG code. The PostGIS insert statement is for adding spatial references to your PostGIS install. David On Wed, Sep 22, 2010 at 12:36 PM, David Fawcett david.fawc...@gmail.comwrote: OK, that makes sense. So, it works well when installing additional SRS' , you won't mess up your stock spatial_ref_sys. The use case that I was going after was where a co-worker wanted to know what srid to use when importing data from a shapefile. I was attempting to use spatialreference.org as a reference to confirm what the standard SRID for this SRS in PostGIS was. I guess that this conflict comes up for SRS' that are part of the stock install. Maybe there is a need for another line on the spatialreference.org page for 'PostGIS SRID' ? Of course, if one already has rights to add data to the db, you can just run a simple (if you know the EPSG code): SELECT * FROM public.spatial_ref_sys WHERE auth_name = 'EPSG' AND auth_srid = 26915; David. On Wed, Sep 22, 2010 at 12:19 PM, David William Bitner bit...@gyttja.org wrote: David, spatialreference.org uses a local SRID with a prepended '9' while maintaining an auth_srid to match EPSG so as not to step on the toes of the SRIDs that are already in the spatial_ref_sys table. While a bit ugly, this is the intended behavior. It uses the '9' because there are no EPSG codes that begin with a 9 so it is a clear namespace to not overwrite an EPSG code regardless of what local SRID identifier is given at spatialreference.org. Clear as mud? David On Wed, Sep 22, 2010 at 11:41 AM, David Fawcett david.fawc...@gmail.com wrote: A minor issue here, but I am looking for some clarification. I am seeking the correct PostGIS srid for UTM Z15N NAD83. If I query spatialreference.org, (http://spatialreference.org/ref/epsg/26915/postgis/) I get: INSERT into spatial_ref_sys (srid, auth_name, auth_srid, proj4text, srtext) values ( 926915, 'epsg', 26915, '+proj=utm +zone=15 +ellps=GRS80 +datum=NAD83 +units=m +no_defs ', 'PROJCS[NAD83 / UTM zone 15N,GEOGCS[NAD83,DATUM[North_American_Datum_1983,SPHEROID[GRS 1980,6378137,298.257222101,AUTHORITY[EPSG,7019]],AUTHORITY[EPSG,6269]],PRIMEM[Greenwich,0,AUTHORITY[EPSG,8901]],UNIT[degree,0.01745329251994328,AUTHORITY[EPSG,9122]],AUTHORITY[EPSG,4269]],UNIT[metre,1,AUTHORITY[EPSG,9001]],PROJECTION[Transverse_Mercator],PARAMETER[latitude_of_origin,0],PARAMETER[central_meridian,-93],PARAMETER[scale_factor,0.9996],PARAMETER[false_easting,50],PARAMETER[false_northing,0],AUTHORITY[EPSG,26915],AXIS[Easting,EAST],AXIS[Northing,NORTH]]'); From a fairly recent of PostGIS, SELECT srid, auth_name, auth_srid FROM public.spatial_ref_sys WHERE auth_srid = 26915 I get: 26915 | EPSG | 26915 I am assuming that the definition at spatial ref.org is wrong? David. ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users -- David William Bitner ___ 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 -- David William Bitner ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
Re: [postgis-users] srid for epsg 26915
There's no such thing as a PostGIS SRID, the SRID only has true meaning local to a database instance. Now, because we happen to install a spatial_ref_sys.sql file which not only has SRID values but makes them identical to the EPSG numbers, there is an assumption that the SRID == EPSG number, but that's not required at all. We could equally have numbered them starting from 1. The auth_name and auth_srid columns give the true external numbers, but the internal srid column is only really valid inside the database. That's why a program reading from PostGIS, to work properly, takes the SRID value from geometry_columns, and looks up either the auth_srid, or the srtext, and uses that definition information to apply an SRS to geometry. Best, Paul On Wed, Sep 22, 2010 at 10:36 AM, David Fawcett david.fawc...@gmail.com wrote: Maybe there is a need for another line on the spatialreference.org page for 'PostGIS SRID' ? ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
[postgis-users] Polygon to Geometry
Hi Ppl, I have a table with a *polygon* type column. Is there any way to change it to geometry type? How could I do it? Thank you for help. Att, PA ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
Re: [postgis-users] Postgis to Text File
Bob - using that sql statement with the COPY command. Examples here: http://www.postgresql.org/docs/8.4/static/sql-copy.html Mark On Wed, Sep 22, 2010 at 1:59 PM, Bob Pawley rjpaw...@shaw.ca wrote: Thanks Henri How do I extract it from the server to a text file?? Bob From: Henri De Feraudy Sent: Wednesday, September 22, 2010 10:09 AM To: PostGIS Users Discussion Subject: Re: [postgis-users] Postgis to Text File Hi, The ST_AsText function is useful in this regards for converting a geometry into Well Known Text. So SELECT ST_AsText(the_geom) from my table; will return the geometry information as strings. The inverse is accomplished with ST_GeomAsText hth Henri From: Bob Pawley rjpaw...@shaw.ca To: postgis-users@postgis.refractions.net Sent: Wed, September 22, 2010 6:01:14 PM Subject: [postgis-users] Postgis to Text File Hi I have forgotten how to extract postgis information to a text file and haven't found any info on it. Any help would be appreciated. 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] Programmatically generate maps zoomed to extent of shapes?
I'm preparing walk lists for a campaign which contains a table of addresses, about 35 to a page on 8.5 x 11 paper. I have the addresses tied into a postgis database with roads and assessor's parcels, so for a given page, I can get a handle on the IDs of the associated shapes. What I'm wondering is would it be possible to generate a map in SVG or PNG/TIFF that would measure 8 x 10.5 and be zoomed to a level that would just include the most opposite parcels. I gather I could define a shape that is all inclusive of the list of shapes for a particular page, but can I then take the coordinates or whatever information derived and submit it to a renderer which then creates a zoomed image of defined dpi (if PNG or TIFF) that would be a representation of a view of all parcels. For example, I have a list of addresses, being 35 representing 3 1/2 city blocks: 200 Main Street 204 ... 535 All of the above addresses tie into specific shape IDs in postgis. I'd like to have a map which would then display a zoomed level so that the parcel of 200 is leftmost and parcel 535 is rightmost of the image. This map would be generated on the fly without human intervention, in other words generated by script. I'm getting my feet wet with various renderers, but have not seen any that might be manipulated or controlled programmatically so that mapes could be generated by scripting. What I am envisioning is having a map of the parcels on the back side of a previous page representing an view of the current page. So, if page three (3) contains the above addresses, the back side of page two (2)would have the map showing all parcels tied to the addresses of page three. That way someone looking at the list on page three could hold up the back side of page 2 in a binder and view a map zoomed to the right level containing all the parcels referenced on page three. I'd prefer to use my own data from postgis, I think this kind of generation would be possible with the Google maps API and possibly creating a container shape that bounds all the shapes from the list. It would be helpful to learn of any renderers, especially ones that expose an application interface. (Hmmm.. now I'm thinking I could generate a large image file of certain sectors and if I could tie the pixels, I could use ImageMagick to slice and dice what I need.) Any rate, this is a wild inquiry which might garner some attention and generate some responses about renderers, especially ones with exposed APIs so they can be manipulated programmatically. -- John L. Poole P.O. Box 6566 Napa, CA 94581-6566 707-812-1323 jlpool...@gmail.com ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
Re: [postgis-users] Efficient SQL for point in field of shapes?
On Tue, Sep 7, 2010 at 1:21 PM, Sean sean...@gmail.com wrote: On Sep 3, 12:55 pm, John Poole jlpool...@gmail.com wrote: I have a table of assessor parcels (polygons) and I want to take a given point (actually will be taking samplings from a geocache tracker, so I'll be doing hundreds, possibly thousands of such queries) and determine which, if any, of the shapes contain the point. The goal is to determine which parcel a tracked person is within and at what times. I'm new to spatial database, and suspect performing a relate or ST_contains against the 40,000 shapes would be horribly inefficient, especially if I have thousands of points. Should I be reading up on indexes, or is there an elegant solution to this problem. I tried searching the archive of this list for point in field of shapes but results were too generic. John L. Poole That shouldn't be very taxing. A bounding box check in the where clause should help speed things up. e.g. select p.id from parcels p left join geocache g on st_contains(p.geom, g.geom) where p.geom g.geom You'll only really know how efficient it is when you try it with real data. Of course, make sure you have a spatial index on the polygons. Sean ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users You're right, it was not taxing, my worry was misplaced and the response times are within acceptable ranges. Thank you -- John L. Poole P.O. Box 6566 Napa, CA 94581-6566 707-812-1323 jlpool...@gmail.com ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
