[postgis-users] Problem with probe_geometry_columns()

[Fred Lehodey]

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 <lehodey at gmail.com> 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.
>
>
>
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/postgis-users/attachments/20100518/be54fd21/attachment.html>