Re: [postgis-users] Problem with probe_geometry_columns()
Thanks Mark and Ben,
I will try to understand better what happens here.
Fred.
On Wed, May 19, 2010 at 4:31 AM, Ben Madin
wrote:
> 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
>
___
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] 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
