Re: [postgis-users] List pre-emption.
You are not alone. Both this list and another I am on that is hosted by Refractions Research will go through periods where message delivery is substantially delayed. Dan On Tue, 2010-05-18 at 20:39 -0700, Ben Madin wrote: > G'day all, > > I have many problems, (and don't want to dwell on them) but I am wondering if > I am the only person who routinely receives list emails in the wrong order - > for instance, I have just replied to request which arrived at 11:24 (only a > few minutes ago) only to discover that others have also replied, at 11:09, > 10:44 and 10:24. > > Just being in the Southern Hemisphere doesn't explain this, nor being > Australian. Is it a gold membership thing to have your questions answered > before you submit them...where do I sign up? More seriously, I haven't > noticed this effect with other (albeit lower volume) lists that I subscribe > to? > > cheers > > Ben > > > ___ > postgis-users mailing list > postgis-users@postgis.refractions.net > http://postgis.refractions.net/mailman/listinfo/postgis-users -- Dan Putler Sauder School of Business University of British Columbia ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
[postgis-users] List pre-emption.
G'day all, I have many problems, (and don't want to dwell on them) but I am wondering if I am the only person who routinely receives list emails in the wrong order - for instance, I have just replied to request which arrived at 11:24 (only a few minutes ago) only to discover that others have also replied, at 11:09, 10:44 and 10:24. Just being in the Southern Hemisphere doesn't explain this, nor being Australian. Is it a gold membership thing to have your questions answered before you submit them...where do I sign up? More seriously, I haven't noticed this effect with other (albeit lower volume) lists that I subscribe to? cheers Ben ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
Re: [postgis-users] Problem with probe_geometry_columns()
Fred,
On 18/05/2010, at 22:38 , Fred Lehodey wrote:
> I have no success trying the function probe_geometry_columns() with Postgis
> 1.5.0
>
> 1) Not sure but comparing the SQL with Postgis 1.3.3:
> the clause (in the INSERT step and not the count of probed)
> "sridcheck.consrc LIKE '(srid('||a.attname||') = %)' in postgis 1.3.3
> is now:
> "sridcheck.consrc LIKE '(st_srid('||a.attname||') = %)' in postgis 1.5.0
> This looks like a tipo error. (this is not the function here but the
> constraint text in pg_constraint)
I think the st_ prefix is now required,
> 2) I have a second problem with pg_constraint table and the "consrc" field.
> Most of time I have something like :
> "(public.srid(the_geom) = 27492)"
> and not (as expected by the function probe_geometry_columns()) :
> "(srid(the_geom) = 27492)"
This was previously an issue if you installed postgis into other than the
public schema. The public schema reference was in a few locations, so you need
to search it out in the function defs and remove it and recreate the function
if you don't want to upgrade.
I have upgraded a number of databases to 1.5 from 1.4 and it seems to have
fixed it... but I have also mucked it by not changing the search_path prior to
running the upgrade, leaving me with multiple postgis function definitions!
cheers
Ben
___
postgis-users mailing list
postgis-users@postgis.refractions.net
http://postgis.refractions.net/mailman/listinfo/postgis-users
Re: [postgis-users] how to use quartum gis to load postgis two tables with join operation, one is with geometry columns, another not.
Build a view with that query. Register the geometry in geometry_columns
table and you're set. But remov that AsText, otherwise you wont have a
geometry column.
George
On Tue, May 18, 2010 at 3:07 PM, sunpeng wrote:
> hi, now,i know how to use quartum gis to load a postgres table with gemetry
> columns: the table is like:
> CREATE TABLE pois
> (
>uid integer not null,
>name VARCHAR(128),
>catcode VARCHAR(32) not null,
>catname VARCHAR(32),
>others VARCHAR(32)
> )
> WITH (
> OIDS = FALSE
> )
> ;
> SELECT AddGeometryColumn('pois', 'location', 4214, 'POINT', 2);
>
> now ,i have another table:
> create table DM_POIS_CLUSTER (
>UID INTEGER not null,
>CLUSTER_ID_0 INTEGER null,
>constraint PK_DM_POIS primary key (UID),
>constraint FK_DM_POIS foreign key (UID) references POIS (UID)
> );
> then ,how to load the following sql result into quartum gis?
> SELECT ST_AsText(ST_ConvexHull(ST_Collect(location))) from
> pois,DM_POIS_CLUSTER goup by CLUSTER_ID_0 ;
>
> it seems qgis only load a table with geometry column.
> thanks a million!
>
> sun peng
>
>
>
> ___
> postgis-users mailing list
> postgis-users@postgis.refractions.net
> http://postgis.refractions.net/mailman/listinfo/postgis-users
>
>
--
George R. C. Silva
Desenvolvimento em GIS
http://blog.geoprocessamento.net
___
postgis-users mailing list
postgis-users@postgis.refractions.net
http://postgis.refractions.net/mailman/listinfo/postgis-users
[postgis-users] How To build postGIS on debian with pbuilder?
Hi all, Does anyone know how to build postGIS using pbuilder on Debian lenny. I have been about to build geos-3.2.0 and proj-4.6.1 It has heartburn with: The following packages have unmet dependencies: pbuilder-satisfydepends-dummy: Depends: postgresql-server-dev-8.4 which is a virtual package. Depends: libproj-dev (>= 4.5.0) which is a virtual package. Both of which are available via apt-get. Any thoughts? I can ask on the debian list also, but I thought people here might provide a more specific solution to this build postgis. -Steve ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
[postgis-users] how to use quartum gis to load postgis two tables with join operation, one is with geometry columns, another not.
hi, now,i know how to use quartum gis to load a postgres table with gemetry
columns: the table is like:
CREATE TABLE pois
(
uid integer not null,
name VARCHAR(128),
catcode VARCHAR(32) not null,
catname VARCHAR(32),
others VARCHAR(32)
)
WITH (
OIDS = FALSE
)
;
SELECT AddGeometryColumn('pois', 'location', 4214, 'POINT', 2);
now ,i have another table:
create table DM_POIS_CLUSTER (
UID INTEGER not null,
CLUSTER_ID_0 INTEGER null,
constraint PK_DM_POIS primary key (UID),
constraint FK_DM_POIS foreign key (UID) references POIS (UID)
);
then ,how to load the following sql result into quartum gis?
SELECT ST_AsText(ST_ConvexHull(ST_Collect(location))) from
pois,DM_POIS_CLUSTER goup by CLUSTER_ID_0 ;
it seems qgis only load a table with geometry column.
thanks a million!
sun peng
___
postgis-users mailing list
postgis-users@postgis.refractions.net
http://postgis.refractions.net/mailman/listinfo/postgis-users
Re: [postgis-users] Bug or Problem with st_transform
Those are the correct answers...
On Tue, May 18, 2010 at 10:15 AM, Charles Galpin wrote:
> Steve, I get the same results on 1.4 and 1.5 (but they differ from yours)
>
> On 1.4
>
> "PostgreSQL 8.4.2, compiled by Visual C++ build 1400, 32-bit"
> "POSTGIS="1.4.1" GEOS="3.2.0-CAPI-1.6.0" PROJ="Rel. 4.6.1, 21 August 2008"
> USE_STATS"
>
> "LINESTRING(0 0,4030 0,4030 4030,0 4030)";
> "LINESTRING(0 0,2.46766330028192e-013 4030,-4030 4030,-4030
> 2.46766330028192e-013)";
> "LINESTRING(0 0,2.21673965870407e-018 0.0362021035411944,-0.0362021059500167
> 0.0362021035411944,-0.0362021059500167 0)";
> "LINESTRING(-71.26162 42.30028,-71.26162 42.3364821035412,-71.29782210595
> 42.3364821035412,-71.29782210595 42.30028)"
>
> On 1.5
>
> "PostgreSQL 8.4.2 on x86_64-redhat-linux-gnu, compiled by GCC gcc (GCC) 4.1.2
> 20071124 (Red Hat 4.1.2-42), 64-bit"
> "POSTGIS="1.5.1" GEOS="3.2.0-CAPI-1.6.0" PROJ="Rel. 4.7.1, 23 September 2009"
> LIBXML="2.6.26" USE_STATS"
>
> "LINESTRING(0 0,4030 0,4030 4030,0 4030)";
> "LINESTRING(0 0,2.46766330028192e-13 4030,-4030 4030,-4030
> 2.46766330028192e-13)";
> "LINESTRING(0 0,2.21673965870407e-18 0.0362021035411944,-0.0362021059500167
> 0.0362021035411944,-0.0362021059500167 0)";
> "LINESTRING(-71.26162 42.30028,-71.26162 42.3364821035412,-71.29782210595
> 42.3364821035412,-71.29782210595 42.30028)"
>
> hth,
> charles
>
> On May 18, 2010, at 11:39 AM, Stephen Woodbridge wrote:
>
>> Mark Cave-Ayland wrote:
>>> Stephen Woodbridge wrote:
Hi guys,
This is looking like it might be a bug on version "POSTGIS="1.3.3"
GEOS="3.0.0-CAPI-1.4.1" PROJ="Rel. 4.6.0, 21 Dec 2007" USE_STATS" which I
know is ancient, but upgrading is not trivial.
What I am trying to do is construct a shape in 900913 in meters and then
rotate, transform it to 4326 and translate it to a location.
The problem is the transform does not look correct. To just rough out the
math, 4030 meters should be about 0.036202 degrees based on
4030/40075016*360 = 0.036202 but st_transform is giving back numbers like
-5.67596869587729e-09
Is this a known problem on this version?
Is my logic faulty?
Is there a better way to do this?
Thanks,
-Steve
>>> Hi Steve,
>>> The first thing I would suggest is that you run the same query on a spare
>>> fresh 1.4/1.5 installation and see what happens. The transformation code
>>> had a good tidy up and some logic corrections within that timeframe,
>>> including better reporting of error messages.
>>
>> Hi Mark, et al,
>>
>> 1) Can someone run this query 1.4 and report back the results:
>>
>> SELECT astext(st_linefromtext('LINESTRING(0 0,4030 0,4030 4030,0 4030)',
>> 900913)),
>> astext(st_rotate(st_linefromtext('LINESTRING(0 0,4030 0,4030 4030,0
>> 4030)', 900913), radians(90))),
>> astext(st_transform(st_rotate(st_linefromtext('LINESTRING(0 0,4030
>> 0,4030 4030,0 4030)', 900913), radians(90)), 4326)),
>>
>> astext(st_translate(st_transform(st_rotate(st_linefromtext('LINESTRING(0
>> 0,4030 0,4030 4030,0 4030)', 900913), radians(90)), 4326),-71.26162,
>> 42.30028));
>>
>> It assumes that you have 900913 loaded in your spatial_ref_sys table.
>>
>> 2) Does anyone know where I can get postgis 1.4 for debian lenny. I can get
>> postgresql 8.4 from from backports.org, but they do not have postgis there.
>> Ideally, at package like postgresql-8.3-postgis based on 1.4 would be
>> simplest, but I'm willing to take a couple of days to dump and reload all my
>> databases to upgrade to postgresql 8.4 also.
>>
>> Thanks,
>> -Steve
>> ___
>> 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] Bug or Problem with st_transform
Steve, I get the same results on 1.4 and 1.5 (but they differ from yours)
On 1.4
"PostgreSQL 8.4.2, compiled by Visual C++ build 1400, 32-bit"
"POSTGIS="1.4.1" GEOS="3.2.0-CAPI-1.6.0" PROJ="Rel. 4.6.1, 21 August 2008"
USE_STATS"
"LINESTRING(0 0,4030 0,4030 4030,0 4030)";
"LINESTRING(0 0,2.46766330028192e-013 4030,-4030 4030,-4030
2.46766330028192e-013)";
"LINESTRING(0 0,2.21673965870407e-018 0.0362021035411944,-0.0362021059500167
0.0362021035411944,-0.0362021059500167 0)";
"LINESTRING(-71.26162 42.30028,-71.26162 42.3364821035412,-71.29782210595
42.3364821035412,-71.29782210595 42.30028)"
On 1.5
"PostgreSQL 8.4.2 on x86_64-redhat-linux-gnu, compiled by GCC gcc (GCC) 4.1.2
20071124 (Red Hat 4.1.2-42), 64-bit"
"POSTGIS="1.5.1" GEOS="3.2.0-CAPI-1.6.0" PROJ="Rel. 4.7.1, 23 September 2009"
LIBXML="2.6.26" USE_STATS"
"LINESTRING(0 0,4030 0,4030 4030,0 4030)";
"LINESTRING(0 0,2.46766330028192e-13 4030,-4030 4030,-4030
2.46766330028192e-13)";
"LINESTRING(0 0,2.21673965870407e-18 0.0362021035411944,-0.0362021059500167
0.0362021035411944,-0.0362021059500167 0)";
"LINESTRING(-71.26162 42.30028,-71.26162 42.3364821035412,-71.29782210595
42.3364821035412,-71.29782210595 42.30028)"
hth,
charles
On May 18, 2010, at 11:39 AM, Stephen Woodbridge wrote:
> Mark Cave-Ayland wrote:
>> Stephen Woodbridge wrote:
>>> Hi guys,
>>>
>>> This is looking like it might be a bug on version "POSTGIS="1.3.3"
>>> GEOS="3.0.0-CAPI-1.4.1" PROJ="Rel. 4.6.0, 21 Dec 2007" USE_STATS" which I
>>> know is ancient, but upgrading is not trivial.
>>>
>>> What I am trying to do is construct a shape in 900913 in meters and then
>>> rotate, transform it to 4326 and translate it to a location.
>>>
>>> The problem is the transform does not look correct. To just rough out the
>>> math, 4030 meters should be about 0.036202 degrees based on
>>> 4030/40075016*360 = 0.036202 but st_transform is giving back numbers like
>>> -5.67596869587729e-09
>>>
>>> Is this a known problem on this version?
>>> Is my logic faulty?
>>> Is there a better way to do this?
>>>
>>> Thanks,
>>> -Steve
>> Hi Steve,
>> The first thing I would suggest is that you run the same query on a spare
>> fresh 1.4/1.5 installation and see what happens. The transformation code had
>> a good tidy up and some logic corrections within that timeframe, including
>> better reporting of error messages.
>
> Hi Mark, et al,
>
> 1) Can someone run this query 1.4 and report back the results:
>
> SELECT astext(st_linefromtext('LINESTRING(0 0,4030 0,4030 4030,0 4030)',
> 900913)),
> astext(st_rotate(st_linefromtext('LINESTRING(0 0,4030 0,4030 4030,0
> 4030)', 900913), radians(90))),
> astext(st_transform(st_rotate(st_linefromtext('LINESTRING(0 0,4030
> 0,4030 4030,0 4030)', 900913), radians(90)), 4326)),
>
> astext(st_translate(st_transform(st_rotate(st_linefromtext('LINESTRING(0
> 0,4030 0,4030 4030,0 4030)', 900913), radians(90)), 4326),-71.26162,
> 42.30028));
>
> It assumes that you have 900913 loaded in your spatial_ref_sys table.
>
> 2) Does anyone know where I can get postgis 1.4 for debian lenny. I can get
> postgresql 8.4 from from backports.org, but they do not have postgis there.
> Ideally, at package like postgresql-8.3-postgis based on 1.4 would be
> simplest, but I'm willing to take a couple of days to dump and reload all my
> databases to upgrade to postgresql 8.4 also.
>
> Thanks,
> -Steve
> ___
> 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] Split Multistring on the_geom
Hi, I would like to know if theres a postgis function (or the easist and effective way), that I can use to split an intersection o 2 multistrings, so I can have the geometric data of --the start and end nodes of both lines, and the new node created in the intersection, I'llbe really greatful, Thank You. -- View this message in context: http://old.nabble.com/Split-Multistring-on-the_geom-tp28597888p28597888.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] Bug or Problem with st_transform
Mark Cave-Ayland wrote:
Stephen Woodbridge wrote:
Hi guys,
This is looking like it might be a bug on version "POSTGIS="1.3.3"
GEOS="3.0.0-CAPI-1.4.1" PROJ="Rel. 4.6.0, 21 Dec 2007" USE_STATS"
which I know is ancient, but upgrading is not trivial.
What I am trying to do is construct a shape in 900913 in meters and
then rotate, transform it to 4326 and translate it to a location.
The problem is the transform does not look correct. To just rough out
the math, 4030 meters should be about 0.036202 degrees based on
4030/40075016*360 = 0.036202 but st_transform is giving back numbers
like -5.67596869587729e-09
Is this a known problem on this version?
Is my logic faulty?
Is there a better way to do this?
Thanks,
-Steve
Hi Steve,
The first thing I would suggest is that you run the same query on a
spare fresh 1.4/1.5 installation and see what happens. The
transformation code had a good tidy up and some logic corrections within
that timeframe, including better reporting of error messages.
Hi Mark, et al,
1) Can someone run this query 1.4 and report back the results:
SELECT astext(st_linefromtext('LINESTRING(0 0,4030 0,4030 4030,0 4030)',
900913)),
astext(st_rotate(st_linefromtext('LINESTRING(0 0,4030 0,4030
4030,0 4030)', 900913), radians(90))),
astext(st_transform(st_rotate(st_linefromtext('LINESTRING(0
0,4030 0,4030 4030,0 4030)', 900913), radians(90)), 4326)),
astext(st_translate(st_transform(st_rotate(st_linefromtext('LINESTRING(0
0,4030 0,4030 4030,0 4030)', 900913), radians(90)), 4326),-71.26162,
42.30028));
It assumes that you have 900913 loaded in your spatial_ref_sys table.
2) Does anyone know where I can get postgis 1.4 for debian lenny. I can
get postgresql 8.4 from from backports.org, but they do not have postgis
there. Ideally, at package like postgresql-8.3-postgis based on 1.4
would be simplest, but I'm willing to take a couple of days to dump and
reload all my databases to upgrade to postgresql 8.4 also.
Thanks,
-Steve
___
postgis-users mailing list
postgis-users@postgis.refractions.net
http://postgis.refractions.net/mailman/listinfo/postgis-users
Re: [postgis-users] Problem with probe_geometry_columns()
Hi Mark,
this is not the function but the constraint definition in the pg_constraint
table. (look at the "consrc" field)
I do a fresh install of 1.5
Fred.
On Tue, May 18, 2010 at 4:00 PM, Mark Cave-Ayland <
mark.cave-ayl...@siriusit.co.uk> wrote:
> Fred Lehodey wrote:
>
> Hi,
>> I have no success trying the function probe_geometry_columns() with
>> Postgis 1.5.0
>>
>> 1) Not sure but comparing the SQL with Postgis 1.3.3:
>> the clause (in the INSERT step and not the count of probed)
>> "sridcheck.consrc LIKE '(*srid*('||a.attname||') = %)' in postgis 1.3.3
>> is now:
>> "sridcheck.consrc LIKE '(*st_srid*('||a.attname||') = %)' in postgis
>> 1.5.0
>> This looks like a tipo error. (this is not the function here but the
>> constraint text in pg_constraint)
>>
>
> No, this is correct. The non ST_ prefix functions have been deprecated
> since PostGIS 1.2-ish. Do you actually have an ST_srid() function in your
> database, e.g. does the following work?
>
> select st_srid(st_geomfromtext('POINT(0 50)', 4326));
>
> Also, did you do a fresh install of 1.5 or did you run the upgrade script
> on an existing 1.3 install?
>
>
> 2) I have a second problem with pg_constraint table and the "consrc"
>> field.
>> Most of time I have something like :
>> "(public.srid(the_geom) = 27492)" and not (as expected by the function
>> probe_geometry_columns()) :
>> "(srid(the_geom) = 27492)"
>>
>> Thanks for any feed-back.
>>
>
> H. Do you have multiple schemas in your database/PostGIS installation?
> Or have you tried to install PostGIS into a specific schema?
>
>
> ATB,
>
> Mark.
>
> --
> Mark Cave-Ayland - Senior Technical Architect
> PostgreSQL - PostGIS
> Sirius Corporation plc - control through freedom
> http://www.siriusit.co.uk
> t: +44 870 608 0063
>
> Sirius Labs: http://www.siriusit.co.uk/labs
> ___
> 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] Problem with probe_geometry_columns()
Fred Lehodey wrote:
Hi,
I have no success trying the function probe_geometry_columns() with
Postgis 1.5.0
1) Not sure but comparing the SQL with Postgis 1.3.3:
the clause (in the INSERT step and not the count of probed)
"sridcheck.consrc LIKE '(*srid*('||a.attname||') = %)' in postgis 1.3.3
is now:
"sridcheck.consrc LIKE '(*st_srid*('||a.attname||') = %)' in postgis 1.5.0
This looks like a tipo error. (this is not the function here but the
constraint text in pg_constraint)
No, this is correct. The non ST_ prefix functions have been deprecated
since PostGIS 1.2-ish. Do you actually have an ST_srid() function in
your database, e.g. does the following work?
select st_srid(st_geomfromtext('POINT(0 50)', 4326));
Also, did you do a fresh install of 1.5 or did you run the upgrade
script on an existing 1.3 install?
2) I have a second problem with pg_constraint table and the "consrc" field.
Most of time I have something like :
"(public.srid(the_geom) = 27492)"
and not (as expected by the function probe_geometry_columns()) :
"(srid(the_geom) = 27492)"
Thanks for any feed-back.
H. Do you have multiple schemas in your database/PostGIS
installation? Or have you tried to install PostGIS into a specific schema?
ATB,
Mark.
--
Mark Cave-Ayland - Senior Technical Architect
PostgreSQL - PostGIS
Sirius Corporation plc - control through freedom
http://www.siriusit.co.uk
t: +44 870 608 0063
Sirius Labs: http://www.siriusit.co.uk/labs
___
postgis-users mailing list
postgis-users@postgis.refractions.net
http://postgis.refractions.net/mailman/listinfo/postgis-users
Re: [postgis-users] Problem with probe_geometry_columns()
I updated the function probe_geometry_columns().
Hope that help.
Fred
-- Function: probe_geometry_columns()
-- DROP FUNCTION probe_geometry_columns();
CREATE OR REPLACE FUNCTION probe_geometry_columns()
RETURNS text AS
$BODY$
DECLARE
inserted integer;
oldcount integer;
probed integer;
stale integer;
BEGIN
SELECT count(*) INTO oldcount FROM geometry_columns;
SELECT count(*) INTO probed
FROM pg_class c, pg_attribute a, pg_type t,
pg_namespace n,
pg_constraint sridcheck, pg_constraint typecheck
WHERE t.typname = 'geometry'
AND a.atttypid = t.oid
AND a.attrelid = c.oid
AND c.relnamespace = n.oid
AND sridcheck.connamespace = n.oid
AND typecheck.connamespace = n.oid
AND sridcheck.conrelid = c.oid
AND sridcheck.consrc LIKE '(%.srid('||a.attname||') = %)'
AND typecheck.conrelid = c.oid
AND typecheck.consrc LIKE
'((%.geometrytype('||a.attname||') = ''%''::text) OR (% IS NULL))'
;
INSERT INTO geometry_columns SELECT
''::varchar as f_table_catalogue,
n.nspname::varchar as f_table_schema,
c.relname::varchar as f_table_name,
a.attname::varchar as f_geometry_column,
2 as coord_dimension,
trim(both ' =)' from
replace(replace(split_part(
sridcheck.consrc, ' = ', 2), ')', ''), '(', ''))::integer AS
srid,
trim(both ' =)''' from substr(typecheck.consrc,
strpos(typecheck.consrc, '='),
strpos(typecheck.consrc, '::')-
strpos(typecheck.consrc, '=')
))::varchar as type
FROM pg_class c, pg_attribute a, pg_type t,
pg_namespace n,
pg_constraint sridcheck, pg_constraint typecheck
WHERE t.typname = 'geometry'
AND a.atttypid = t.oid
AND a.attrelid = c.oid
AND c.relnamespace = n.oid
AND sridcheck.connamespace = n.oid
AND typecheck.connamespace = n.oid
AND sridcheck.conrelid = c.oid
AND sridcheck.consrc LIKE '(%.srid('||a.attname||') = %)'
AND typecheck.conrelid = c.oid
AND typecheck.consrc LIKE
'((%.geometrytype('||a.attname||') = ''%''::text) OR (% IS NULL))'
AND NOT EXISTS (
SELECT oid FROM geometry_columns gc
WHERE c.relname::varchar = gc.f_table_name
AND n.nspname::varchar = gc.f_table_schema
AND a.attname::varchar = gc.f_geometry_column
);
GET DIAGNOSTICS inserted = ROW_COUNT;
IF oldcount > probed THEN
stale = oldcount-probed;
ELSE
stale = 0;
END IF;
RETURN 'probed:'||probed::text||
' inserted:'||inserted::text||
' conflicts:'||(probed-inserted)::text||
' stale:'||stale::text;
END
$BODY$
LANGUAGE 'plpgsql' VOLATILE
COST 100;
ALTER FUNCTION probe_geometry_columns() OWNER TO postgres;
COMMENT ON FUNCTION probe_geometry_columns() IS 'Scans all tables with
PostGIS geometry constraints and adds them to the geometry_columns table if
they are not there.';
On Tue, May 18, 2010 at 3:38 PM, Fred Lehodey wrote:
> Hi,
> I have no success trying the function probe_geometry_columns() with Postgis
> 1.5.0
>
> 1) Not sure but comparing the SQL with Postgis 1.3.3:
> the clause (in the INSERT step and not the count of probed)
> "sridcheck.consrc LIKE '(*srid*('||a.attname||') = %)' in postgis 1.3.3
> is now:
> "sridcheck.consrc LIKE '(*st_srid*('||a.attname||') = %)' in postgis
> 1.5.0
> This looks like a tipo error. (this is not the function here but the
> constraint text in pg_constraint)
>
>
> 2) I have a second problem with pg_constraint table and the "consrc" field.
> Most of time I have something like :
> "(public.srid(the_geom) = 27492)"
> and not (as expected by the function probe_geometry_columns()) :
> "(srid(the_geom) = 27492)"
>
> Thanks for any feed-back.
>
> Fred.
>
>
>
___
postgis-users mailing list
postgis-users@postgis.refractions.net
http://postgis.refractions.net/mailman/listinfo/postgis-users
[postgis-users] Problem with probe_geometry_columns()
Hi,
I have no success trying the function probe_geometry_columns() with Postgis
1.5.0
1) Not sure but comparing the SQL with Postgis 1.3.3:
the clause (in the INSERT step and not the count of probed)
"sridcheck.consrc LIKE '(*srid*('||a.attname||') = %)' in postgis 1.3.3
is now:
"sridcheck.consrc LIKE '(*st_srid*('||a.attname||') = %)' in postgis 1.5.0
This looks like a tipo error. (this is not the function here but the
constraint text in pg_constraint)
2) I have a second problem with pg_constraint table and the "consrc" field.
Most of time I have something like :
"(public.srid(the_geom) = 27492)"
and not (as expected by the function probe_geometry_columns()) :
"(srid(the_geom) = 27492)"
Thanks for any feed-back.
Fred.
___
postgis-users mailing list
postgis-users@postgis.refractions.net
http://postgis.refractions.net/mailman/listinfo/postgis-users
Re: [postgis-users] implement your own index
Mark, many thanks for all these very useful pointers. B. Zitat von Mark Cave-Ayland : Biddy wrote: María, thank you for the fast reply. However, these docs refer to the usage of GIST. If I understand it correctly, an R-tree is implemented on top of GIST. What if I don't want to use an R-tree? If I wanted to implement my own ...let's say I want to implement a quadtree or some other new, fancy index. How would I go about doing it? The GIST API currently does not support space partition-based nor ordered indices (see http://www.postgresql.org/docs/8.4/interactive/gist-implementation.html for the exact details). So if you wanted to implement a quadtree or nearest neighbour type index searches then you'd need to alter the GIST API or invent another index AM in PostgreSQL first. You may find the SP-GIST project interesting in this respect: http://www.cs.purdue.edu/spgist/. Additionally, is there a way to change the attributes with which the R-tree is built? Or at least see how the R-tree is built? It depends what attributes you are trying to modify. If they can be defined in terms of the GIST picksplit function then it is likely possible. In terms of visualising the R-Tree itself, Oleg and Teodor's gevel module from http://www.sai.msu.su/~megera/postgres/gist/ can be used as an aid to generate output suitable for rendering. HTH, Mark. -- Mark Cave-Ayland - Senior Technical Architect PostgreSQL - PostGIS Sirius Corporation plc - control through freedom http://www.siriusit.co.uk t: +44 870 608 0063 Sirius Labs: http://www.siriusit.co.uk/labs ___ 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] Bug or Problem with st_transform
Stephen Woodbridge wrote: Hi guys, This is looking like it might be a bug on version "POSTGIS="1.3.3" GEOS="3.0.0-CAPI-1.4.1" PROJ="Rel. 4.6.0, 21 Dec 2007" USE_STATS" which I know is ancient, but upgrading is not trivial. What I am trying to do is construct a shape in 900913 in meters and then rotate, transform it to 4326 and translate it to a location. The problem is the transform does not look correct. To just rough out the math, 4030 meters should be about 0.036202 degrees based on 4030/40075016*360 = 0.036202 but st_transform is giving back numbers like -5.67596869587729e-09 Is this a known problem on this version? Is my logic faulty? Is there a better way to do this? Thanks, -Steve Hi Steve, The first thing I would suggest is that you run the same query on a spare fresh 1.4/1.5 installation and see what happens. The transformation code had a good tidy up and some logic corrections within that timeframe, including better reporting of error messages. HTH, Mark. -- Mark Cave-Ayland - Senior Technical Architect PostgreSQL - PostGIS Sirius Corporation plc - control through freedom http://www.siriusit.co.uk t: +44 870 608 0063 Sirius Labs: http://www.siriusit.co.uk/labs ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
[postgis-users] Bug or Problem with st_transform
Hi guys,
This is looking like it might be a bug on version "POSTGIS="1.3.3"
GEOS="3.0.0-CAPI-1.4.1" PROJ="Rel. 4.6.0, 21 Dec 2007" USE_STATS" which
I know is ancient, but upgrading is not trivial.
What I am trying to do is construct a shape in 900913 in meters and then
rotate, transform it to 4326 and translate it to a location.
The problem is the transform does not look correct. To just rough out
the math, 4030 meters should be about 0.036202 degrees based on
4030/40075016*360 = 0.036202 but st_transform is giving back numbers
like -5.67596869587729e-09
Is this a known problem on this version?
Is my logic faulty?
Is there a better way to do this?
Thanks,
-Steve
900913 definition:
900913;"spatialreference.org";900913;"PROJCS["unnamed",GEOGCS["unnamed
ellipse",DATUM["unknown",
SPHEROID["unnamed",6378137,0]],PRIMEM["Greenwich",0],
UNIT["degree",0.0174532925199433]],PROJECTION["Mercator_2SP"],
PARAMETER["standard_parallel_1",0],PARAMETER["central_meridian",0],
PARAMETER["false_easting",0],PARAMETER["false_northing",0],
UNIT["Meter",1],
EXTENSION["PROJ4","+proj=merc +a=6378137 +b=6378137 +lat_ts=0.0
+lon_0=0.0 +x_0=0.0 +y_0=0 +k=1.0 +units=m +nadgri...@null +wktext
+no_defs"]]";"+proj=merc +a=6378137 +b=6378137 +lat_ts=0.0 +lon_0=0.0
+x_0=0.0 +y_0=0
+k=1.0 +units=m +nadgri...@null +wktext +no_defs"
Test query showing problem:
SELECT astext(st_linefromtext('LINESTRING(0 0,4030 0,4030 4030,0 4030)',
900913)),
astext(st_rotate(st_linefromtext('LINESTRING(0 0,4030 0,4030
4030,0 4030)', 900913), radians(90))),
astext(st_transform(st_rotate(st_linefromtext('LINESTRING(0
0,4030 0,4030 4030,0 4030)', 900913), radians(90)), 4326)),
astext(st_translate(st_transform(st_rotate(st_linefromtext('LINESTRING(0
0,4030 0,4030 4030,0 4030)', 900913), radians(90)), 4326),-71.26162,
42.30028))
I also get messages:
WARNING: transform: -38 (failed to load NAD27-83 correction file)
WARNING: transform: -38 (failed to load NAD27-83 correction file)
WARNING: transform: -38 (failed to load NAD27-83 correction file)
WARNING: transform: -38 (failed to load NAD27-83 correction file)
WARNING: transform: -38 (failed to load NAD27-83 correction file)
WARNING: transform: -38 (failed to load NAD27-83 correction file)
WARNING: transform: -38 (failed to load NAD27-83 correction file)
WARNING: transform: -38 (failed to load NAD27-83 correction file)
The results are:
"LINESTRING(0 0,4030 0,4030 4030,0 4030)";
"LINESTRING(0 0,2.46766330028192e-13 4030,-4030 4030,-4030
2.46766330028192e-13)";
"LINESTRING(0 0,3.47552844773335e-25
5.67596699566489e-09,-5.67596869587729e-09
5.67596699566489e-09,-5.67596869587729e-09 0)";
"LINESTRING(-71.26162 42.30028,-71.26162
42.300280005676,-71.261620005676 42.300280005676,-71.261620005676
42.30028)"
___
postgis-users mailing list
postgis-users@postgis.refractions.net
http://postgis.refractions.net/mailman/listinfo/postgis-users
Re: [postgis-users] Problem with postgis querybypoint in mapserver-php
Thank you very much!
I have tried a lot of thigs, but I will test your ideas. And as I said, nobody
responded me in the Mapserver list.
Relly thank you for you response!
Guillermo
Mensaje citado por Ben Madin :
> Guillermo,
>
> MapServer is the proper list for this question, but you haven't given much
> information on what is a complex issue... and I can't help you directly with
> it I'm sorry, but it might help if you gave a bit more information.
>
> Are you getting a map? Is the data correctly displayed? You could get
> attribute data either from the data in your query (use * in the format below
> if in doubt) or if you take the gid and look it up separately.
>
> The MapServer Docs at http://mapserver.org/input/vector/postgis.html show an
> example of a query:
>
> CONNECTIONTYPE POSTGIS
> CONNECTION "dbname=yourdatabasename user=yourdbusername"
> DATA "the_geom from (select g.gid, g.the_geom, a.attr1, a.attr2 from geotable
> g join attrtable a on g.gid = a.aid) as subquery unique gid using srid=4326"
>
> A common mistake is a mismatch between the projection systems - are they both
> displayed?
>
> Have you been logging the php and MapServer output (DEBUG level 1 or more) to
> see what is happening when you click on a point?
>
> Having said that, I notice you have suppressed any error messages on
>
> > @$qlayer->queryByPoint($point, MS_MULTIPLE, $radius);
>
> so you may not get much help from the php log.
>
> hopefully someone else might be able to help, good luck.
>
> cheers
>
> Ben
>
>
>
> On 17/05/2010, at 15:09 , Guillermo Tamburini Beliveau wrote:
>
> >
> > Hi,
> >
> > first, I would like to apologize for possible confussions with my english
> or my
> > limitated knowledge of programming and the explanations on this.
> >
> > And second, sorry if I insist, but I trided two times in the mapserver list
> and
> > now, the second time in this, and nobody responds me. please, if somebody
> knows
> > anything about my question, please say something. Thank you.
> >
> > I would ask to the list if someone knows the reason of a problem that I'm
> > having with the php-mapscript methods for the query methods in the postgis
> > layers.
> >
> > Indistinctly for querybypoint or querbyrectangle, my code (different
> versions of
> > it), works perfectly with shapes, but it always fails with the postgis
> layers.
> > When calling to the shapeindex member of the ResultCacheMemberObj obtained,
> it
> > always returns 0, as is contrary to what happens with the shapes, where it
> > always returns the correct shapeindex. Then, it is impossible to acces to
> the
> > desidered feature, and as you know, this are usal and simple actions.
> >
> > May I have to add more columns to the select of the .map file for getting
> the
> > features atributes like in the example (I don't think so)?
> >
> >DATA "geom FROM puntos using unique gid"
> > #DATA "geom FROM (select gid , nombre, tramo, geom from puntos) as foo
> using
> > unique geom"
> > #DATA "geom FROM (select * from puntos) as foo using unique gid"
> >
> > Or there is some paramter in the configuration files of mapserver or php
> that I
> > have to change.
> >
> > There is the code of the function:
> >
> > function CercaPunto($point,$map,$radius) {
> >
> > $qlayer = $map->getLayerByName('trazado');
> > $qlayer->set("tolerance",$radius);
> > @$qlayer->queryByPoint($point, MS_MULTIPLE, $radius);
> > $numResults = $qlayer->getNumResults();
> > if ($numResults != 0) {
> > for ($i = 0; $i < $numResults; $i++) {
> > $query_result = $qlayer->getResult($i);
> > $Lista_ele[$i] = $query_result->shapeindex; // Here is the
> error
> > with postgres
> > }
> > } else {
> > $valido =0;
> > $Lista_ele = "";
> > }
> > return $Lista_ele;
> > } // end CercaPunto
> >
> >
> > Thank you very much.
> >
> > Guillermo Tamburini
> >
> >
> >
> > ___
> > postgis-users mailing list
> > postgis-users@postgis.refractions.net
> > http://postgis.refractions.net/mailman/listinfo/postgis-users
> >
> > - Fin del mensaje reenviado -
> >
> >
> >
> > ___
> > 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] OpenStreetMap + osm2pgsql + pgRouting into Postgis
El Tuesday 18 May 2010, Roshni Budhrani escribió: > I have been told that this is probably because the OSM data is not > properly noded at all intersections. If it is so, then how can i fix my > data? Most probably. You can try to use a bigger number on the assign_vertex_id function, but it can lead you to wrong results too. -- 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] OpenStreetMap + osm2pgsql + pgRouting into Postgis
Hi,
could you please help me into this matter. The scenario is the
following:
I have got OSM data that has been inserted into PostGIS using osm2pgsql.
I have used MapServer to interact with my map through OpenLayers and this
works perfectly fine.
On the other side, I need to use pgRouting to use the algoritms of
shortest_path or dijkstra to find a route between A and B and show it with
OpenLayers. I have managed to do this with OpenLayers, so i know that if the
algorithms return any result, the calculated rout shows properly in my
browser. BUT, my problem is that in most of the cases, my algoritms do not
return any result... lets say maybe 80-90% of the times there is no route
calculated. What can i do ??
My geom table has got the values x1,y1,x2,y2,source,target filled for
all the rows.
The columns source and target are filled using this:
SELECT assign_vertex_id('planet_osm_line', 5, 'the_geom', 'gid');
As for the columns x1,y1,x2,y2, these are filled using this:
SELECT astext(StartPoint(way)) as startpoint from planet_osm_line =>
used to fill up x1, y1
SELECT astext(EndPoint(way))as endpoint from planet_osm_line => used to
fill up x2, y2
I really dont know where the problem could be, as all the rows are
filled up and the data seem correct...
Looking further into the matter,
i have noticed that the only cases where i have managed to get some
result with the shortest_path function is when there is a straight line
between A and B, but if i need to do A and C, and there is no straight line
between them (suppose something like A => B => C), then no result will be
returned :(
The same applies for other routing algorithms ... and still no clue
I have been told that this is probably because the OSM data is not
properly noded at all intersections. If it is so, then how can i fix my
data?
Thank you all
___
postgis-users mailing list
postgis-users@postgis.refractions.net
http://postgis.refractions.net/mailman/listinfo/postgis-users
Re: [postgis-users] implement your own index
Biddy wrote: María, thank you for the fast reply. However, these docs refer to the usage of GIST. If I understand it correctly, an R-tree is implemented on top of GIST. What if I don't want to use an R-tree? If I wanted to implement my own ...let's say I want to implement a quadtree or some other new, fancy index. How would I go about doing it? The GIST API currently does not support space partition-based nor ordered indices (see http://www.postgresql.org/docs/8.4/interactive/gist-implementation.html for the exact details). So if you wanted to implement a quadtree or nearest neighbour type index searches then you'd need to alter the GIST API or invent another index AM in PostgreSQL first. You may find the SP-GIST project interesting in this respect: http://www.cs.purdue.edu/spgist/. Additionally, is there a way to change the attributes with which the R-tree is built? Or at least see how the R-tree is built? It depends what attributes you are trying to modify. If they can be defined in terms of the GIST picksplit function then it is likely possible. In terms of visualising the R-Tree itself, Oleg and Teodor's gevel module from http://www.sai.msu.su/~megera/postgres/gist/ can be used as an aid to generate output suitable for rendering. HTH, Mark. -- Mark Cave-Ayland - Senior Technical Architect PostgreSQL - PostGIS Sirius Corporation plc - control through freedom http://www.siriusit.co.uk t: +44 870 608 0063 Sirius Labs: http://www.siriusit.co.uk/labs ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
Re: [postgis-users] implement your own index
María, thank you for the fast reply. However, these docs refer to the usage of GIST. If I understand it correctly, an R-tree is implemented on top of GIST. What if I don't want to use an R-tree? If I wanted to implement my own ...let's say I want to implement a quadtree or some other new, fancy index. How would I go about doing it? Additionally, is there a way to change the attributes with which the R-tree is built? Or at least see how the R-tree is built? Many thanks, B. Zitat von Maria Arias de Reyna : El Tuesday 18 May 2010, Biddy escribió: write your own spatial index. Do you mean this: http://postgis.refractions.net/docs/ch04.html#id2794434 ? Take a look at the docs, probably most of your questions are answered there. -- 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 mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
Re: [postgis-users] implement your own index
El Tuesday 18 May 2010, Biddy escribió: > write your own spatial index. Do you mean this: http://postgis.refractions.net/docs/ch04.html#id2794434 ? Take a look at the docs, probably most of your questions are answered there. -- 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] implement your own index
Hello everyone, I am new to PostGIS and I was wondering if anybody could give me some information on (or provide me with a pointer to information on) how to write your own spatial index. All help is greatly appreciated, many thanks, B. ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
Re: [postgis-users] Problem: Save a postgis layer as a shape file
El Tuesday 18 May 2010, Anita Van Deventer escribió: > I want to save a postgis layer as a shape file - it returns an error that > it cannot create an attribute field. Any suggestions? How are you saving it as a shape file and what error does it shows exactly? -- 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] Problem: Save a postgis layer as a shape file
I want to save a postgis layer as a shape file - it returns an error that it cannot create an attribute field. Any suggestions? Anita -- This message is subject to the CSIR's copyright terms and conditions, e-mail legal notice, and implemented Open Document Format (ODF) standard. The full disclaimer details can be found at http://www.csir.co.za/disclaimer.html. This message has been scanned for viruses and dangerous content by MailScanner, and is believed to be clean. MailScanner thanks Transtec Computers for their support. ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
