Re: [postgis-users] SRID in geometry_columns view
On Wed, Jul 25, 2012 at 04:40:23PM +1200, Mike Toews wrote: > On 25 July 2012 13:48, Richard Greenwood 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. Could any of you guys file a ticket with a patch for this ? TIA --strk; ,--o-. | __/ |Delivering high quality PostGIS 2.1 | / 2.1 |http://strk.keybit.net - http://vizzuality.com `-o--' ___ 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 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
[postgis-users] Maven Repository for PostGIS 2.0.1
Hi, Has PostGIS 2.0.1 been uploaded to any Maven repository? I couldn't find it in the central repository anywhere... Thanks Ronak Patel___ 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 Tue, Jul 24, 2012 at 4:10 PM, Mike Toews wrote: > On 25 July 2012 09:49, Richard Greenwood 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. 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? >> 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. My issue is trying to get the the geometry_columns view to recognize my srid, not geometry type, but I did try specific geometry types which produced the same error. > 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); 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. Using ST_SetSRID() as you suggest above in the creation of the view works in so far as that it doesn't generate an error when I create my view, but geometry_columns still lists a srid of 0 for my view. Thanks for your suggestions, but I still don't get it. Rich -- Richard Greenwood richard.greenw...@gmail.com www.greenwoodmap.com ___ 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 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
[postgis-users] SRID in geometry_columns view
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 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) Any suggestions would be appreciated. Rich [1] http://postgis.refractions.net/docs/using_postgis_dbmanagement.html#geometry_columns -- Richard Greenwood richard.greenw...@gmail.com www.greenwoodmap.com ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
Re: [postgis-users] importing excel spreadsheet
Kauth, Matthew eb.com> writes: > Hi all. I’m working on a project with my job and have created an excel > spreadsheet consisting of over 1000 polygon coordinates. > Per postgres I have > the coordinates in the following fashion > long,lat;long,lat;long,lat, and so on. What do you mean "per postgres"? This format isn't a standard that I know of. A link would be interesting. > My question, if someone can help, is how do I import this > spreadsheet into > postgres 9.1? In outline, in case you have never done this before: * Save the spreadsheet as tab-delimited text (Excel == yuck). * Process this file to change the coordinates into "well known text" format (I would script the conversion with awk) and save as a new file. Script little clean ups here as well. * Open the new text file in a real text editor to inspect and clean. * Load this new file into Postgres with the COPY statement. * Then convert the WKT column to a geometry column with ST_GeomFromEWKT(). WKT example: http://postgis.refractions.net/documentation/manual-svn/using_postgis_dbmanagement.html#OpenGISWKBWKT If you get stuck, try to give us an example of your data (cut and paste or use pastebin) and what steps exactly you are doing. If the above doesn't make any sense or is too high level, ask more specific questions about each step. Note that I am assuming you have access to a functional Unix-like command line. If not ... get a real computer ;) ... ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
Re: [postgis-users] ST_Covers for Geography
I think this is my answer to ST_Covers and ST_CoveredBy: http://trac.osgeo.org/postgis/ticket/524 -- View this message in context: http://postgis.17.n6.nabble.com/ST-Covers-for-Geography-tp4999120p4999125.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
Re: [postgis-users] ST_Covers for Geography
I was getting the same error with ST_CoveredBy. Is that expected? Is this something expected? I saw some chatter in the dev logs that this might be added in version 2.1, is there a chance for that? Here is what I see in the trunk: -- Only implemented for polygon-over-point 668 -- Availability: 1.5.0 669 CREATE OR REPLACE FUNCTION ST_CoveredBy(geography, geography) 670 RETURNS boolean 671 AS 'SELECT $1 && $2 AND _ST_Covers($2, $1)' 672 LANGUAGE 'sql' IMMUTABLE ; -- View this message in context: http://postgis.17.n6.nabble.com/ST-Covers-for-Geography-tp4999120p4999124.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
Re: [postgis-users] ST_Covers for Geography
Try out ST_CoveredBy, I think that's what you're looking for. -- View this message in context: http://postgis.17.n6.nabble.com/ST-Covers-for-Geography-tp4999120p4999123.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
Re: [postgis-users] ST_Covers for Geography
Is there anything that does a st_x(geog,geog) [Both simple rectangle polygons] that is similar to a st_covers, or st_within? -- View this message in context: http://postgis.17.n6.nabble.com/ST-Covers-for-Geography-tp4999120p4999122.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
Re: [postgis-users] ST_Covers for Geography
Poorly worded error message: only calls of the form st_covers(polygon,
point) are supported. st_covers(poly, poly) is not
P.
On Tue, Jul 24, 2012 at 11:53 AM, DrYSG wrote:
> I am getting this error:
> *"geography_covers: only POLYGON and POINT types are currently supported"*
> But I can't see what is wrong. Especially, since I have an ST_Intersects
> that matches this request, but does not return the error. As far as I can
> tell, I am passing in Polygons (cat.gpoly) is a geography polygon.
>
> ST_Covers version:
>
> *SELECT
> cat.idx,cat.size_bytes,cat.date,cat.type,cat.elevation,cat.source,cat.egpl_date,cat.classification,cat.classification_int,cat.handling,cat.originator,cat.datum,cat.product,cat.description,cat.bbox,cat.path,cat.year
> FROM portal.catalog AS cat
> WHERE ST_Covers(st_geogfromtext('SRID=4326;POLYGON((-70.88758389282220
> 42.50890460863670,-71.24841610717770 42.50890460863670,-71.24841610717770
> 42.22276957602410,-70.88758389282220 42.22276957602410,-70.88758389282220
> 42.50890460863670))'), cat.gpoly) AND (cat.date >= '1/1/1900 5:00:00 AM' AND
> cat.date <= '7/24/2012 2:45:06 PM') LIMIT 5000;*
>
> ST_Intersects version: (which works fine) and has the same values:
>
> *SELECT
> cat.idx,cat.size_bytes,cat.date,cat.type,cat.elevation,cat.source,cat.egpl_date,cat.classification,cat.classification_int,cat.handling,cat.originator,cat.datum,cat.product,cat.description,cat.bbox,cat.path,cat.year
> FROM portal.catalog AS cat
> WHERE ST_Intersects(st_geogfromtext('SRID=4326;POLYGON((-70.88758389282220
> 42.50890460863670,-71.24841610717770 42.50890460863670,-71.24841610717770
> 42.22276957602410,-70.88758389282220 42.22276957602410,-70.88758389282220
> 42.50890460863670))'), cat.gpoly) AND (cat.date >= '1/1/1900 5:00:00 AM' AND
> cat.date <= '7/24/2012 2:45:03 PM') LIMIT 5000;*
>
> -- -1 row(s) affected.
>
>
>
> --
> View this message in context:
> http://postgis.17.n6.nabble.com/ST-Covers-for-Geography-tp4999120.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
[postgis-users] ST_Covers for Geography
I am getting this error:
*"geography_covers: only POLYGON and POINT types are currently supported"*
But I can't see what is wrong. Especially, since I have an ST_Intersects
that matches this request, but does not return the error. As far as I can
tell, I am passing in Polygons (cat.gpoly) is a geography polygon.
ST_Covers version:
*SELECT
cat.idx,cat.size_bytes,cat.date,cat.type,cat.elevation,cat.source,cat.egpl_date,cat.classification,cat.classification_int,cat.handling,cat.originator,cat.datum,cat.product,cat.description,cat.bbox,cat.path,cat.year
FROM portal.catalog AS cat
WHERE ST_Covers(st_geogfromtext('SRID=4326;POLYGON((-70.88758389282220
42.50890460863670,-71.24841610717770 42.50890460863670,-71.24841610717770
42.22276957602410,-70.88758389282220 42.22276957602410,-70.88758389282220
42.50890460863670))'), cat.gpoly) AND (cat.date >= '1/1/1900 5:00:00 AM' AND
cat.date <= '7/24/2012 2:45:06 PM') LIMIT 5000;*
ST_Intersects version: (which works fine) and has the same values:
*SELECT
cat.idx,cat.size_bytes,cat.date,cat.type,cat.elevation,cat.source,cat.egpl_date,cat.classification,cat.classification_int,cat.handling,cat.originator,cat.datum,cat.product,cat.description,cat.bbox,cat.path,cat.year
FROM portal.catalog AS cat
WHERE ST_Intersects(st_geogfromtext('SRID=4326;POLYGON((-70.88758389282220
42.50890460863670,-71.24841610717770 42.50890460863670,-71.24841610717770
42.22276957602410,-70.88758389282220 42.22276957602410,-70.88758389282220
42.50890460863670))'), cat.gpoly) AND (cat.date >= '1/1/1900 5:00:00 AM' AND
cat.date <= '7/24/2012 2:45:03 PM') LIMIT 5000;*
-- -1 row(s) affected.
--
View this message in context:
http://postgis.17.n6.nabble.com/ST-Covers-for-Geography-tp4999120.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] importing excel spreadsheet
Hi all. I'm working on a project with my job and have created an excel spreadsheet consisting of over 1000 polygon coordinates. Per postgres I have the coordinates in the following fashion long,lat;long,lat;long,lat, and so on. My question, if someone can help, is how do I import this spreadsheet into postgres 9.1? ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
Re: [postgis-users] Trouble upgrading to 2.0.1
The spatial_ref_sys table has no sequence by default.
-bborie
On 07/24/2012 11:03 AM, Hays Barrett wrote:
> Sorry for the late reply.
> We tried installing postgis both the old and new way before running
> postgis_restore.pl and both gave the same results.
> I think one of the developers is up to some funny business. should
> spatial_ref_sys_srid_seq even exist? I think this was created by us.
> spatial_ref_sys shouldn't even have a sequence right?
>
> On Fri, Jul 20, 2012 at 11:30 AM, Sandro Santilli wrote:
>
>> On Fri, Jul 20, 2012 at 11:13:35AM -0600, Hays Barrett wrote:
>>> Hello all. I am trying to upgrade to postgis-2.0.1.
>>>
>>> I dumped my db with :
>>> pg_dump -Fc -b -v -f "/opt/dump/dump.sql" postgis
>>>
>>> When I run "./postgis_restore.pl "/opt/dump/dump.sql" | psql -a
>> postgis2" I
>>> get 2 errors.
>>>
>>> ALTER SEQUENCE spatial_ref_sys_srid_seq OWNED BY spatial_ref_sys.srid;
>>> ERROR: sequence must have same owner as table it is linked to
>>> SELECT pg_catalog.setval('spatial_ref_sys_srid_seq', 1, false);
>>>
>>>
>>> ALTER TABLE spatial_ref_sys ADD PRIMARY KEY(srid);
>>> ERROR: could not create unique index "spatial_ref_sys_pkey"
>>> DETAIL: Key (srid)=(4279) is duplicated.
>>
>> This is really unexpected, I'm surprised it happens.
>> Did you install the spatial_ref_sys.sql in "postgis2" database
>> prior to sending the postgis_restore.pl output ?
>> Did you get any notice on stderr from postgis_restore.pl ?
>>
>> --strk;
>>
>> ,--o-.
>> | __/ |Delivering high quality PostGIS 2.1
>> | / 2.1 |http://strk.keybit.net - http://vizzuality.com
>> `-o--'
>>
>>
>
>
>
>
> ___
> postgis-users mailing list
> postgis-users@postgis.refractions.net
> http://postgis.refractions.net/mailman/listinfo/postgis-users
>
--
Bborie Park
Programmer
Center for Vectorborne Diseases
UC Davis
530-752-8380
bkp...@ucdavis.edu
___
postgis-users mailing list
postgis-users@postgis.refractions.net
http://postgis.refractions.net/mailman/listinfo/postgis-users
Re: [postgis-users] Trouble upgrading to 2.0.1
Sorry for the late reply.
We tried installing postgis both the old and new way before running
postgis_restore.pl and both gave the same results.
I think one of the developers is up to some funny business. should
spatial_ref_sys_srid_seq even exist? I think this was created by us.
spatial_ref_sys shouldn't even have a sequence right?
On Fri, Jul 20, 2012 at 11:30 AM, Sandro Santilli wrote:
> On Fri, Jul 20, 2012 at 11:13:35AM -0600, Hays Barrett wrote:
> > Hello all. I am trying to upgrade to postgis-2.0.1.
> >
> > I dumped my db with :
> > pg_dump -Fc -b -v -f "/opt/dump/dump.sql" postgis
> >
> > When I run "./postgis_restore.pl "/opt/dump/dump.sql" | psql -a
> postgis2" I
> > get 2 errors.
> >
> > ALTER SEQUENCE spatial_ref_sys_srid_seq OWNED BY spatial_ref_sys.srid;
> > ERROR: sequence must have same owner as table it is linked to
> > SELECT pg_catalog.setval('spatial_ref_sys_srid_seq', 1, false);
> >
> >
> > ALTER TABLE spatial_ref_sys ADD PRIMARY KEY(srid);
> > ERROR: could not create unique index "spatial_ref_sys_pkey"
> > DETAIL: Key (srid)=(4279) is duplicated.
>
> This is really unexpected, I'm surprised it happens.
> Did you install the spatial_ref_sys.sql in "postgis2" database
> prior to sending the postgis_restore.pl output ?
> Did you get any notice on stderr from postgis_restore.pl ?
>
> --strk;
>
> ,--o-.
> | __/ |Delivering high quality PostGIS 2.1
> | / 2.1 |http://strk.keybit.net - http://vizzuality.com
> `-o--'
>
>
--
--
Hays Barrett
Cell (505) 886-1574
Office (505) 277-3622 Ext248
--
___
postgis-users mailing list
postgis-users@postgis.refractions.net
http://postgis.refractions.net/mailman/listinfo/postgis-users
[postgis-users] drawQuery() takes forever...
Hello,
I have a postgres query, which takes about 0,1sek and resultset is one line On
my page, there is a link, which uses same sql tuo print koordinates to map.
When plotting to the maps, it's never ends.
I killed query from active task and pick sql from postgres log. It seems like
following:
select encode(AsBinary(force_collection(force_2d("koord")),'NDR'),'hex') as geom,"oid" from
(
select ... [subpressed query]
) as foobar
where koord && GeomFromText('POLYGON((3517739 7581169,3517739 7587169,3523739 7587169,3523739 7581169,3517739 7581169))',find_srid('','location','koord'))
When trying same with phpPgAdmin, first just my sql from same sql-sentence.
select ... [subpressed query]
It takes same 0,1sek and one line as result.
When trying same query as whole
select encode(AsBinary(force_collection(force_2d("koord")),'NDR'),'hex') as geom,"oid" from
(
select ... [subpressed query]
) as foobar
where koord && GeomFromText('POLYGON((3517739 7581169,3517739 7587169,3523739 7587169,3523739 7581169,3517739 7581169))',find_srid('','location','koord'))
It takes long time. After 15minutes, I killed query again.
Any idea, why this hungs?
Am I doing something wrongly?
How to trace this?
--
Pena
--
Wippies-vallankumous on täällä! Varmista paikkasi vallankumouksen eturintamassa
ja liity Wippiesiin heti!
http://www.wippies.com/
___
postgis-users mailing list
postgis-users@postgis.refractions.net
http://postgis.refractions.net/mailman/listinfo/postgis-users
