 |
Caro-COOPS.org Discussion Board
Discussion area for Caro-COOPS-related initiatives
|
| View previous topic :: View next topic |
| Author |
Message |
cpurvis
Joined: 24 Feb 2025
Posts: 255
|
| Posted: Wed Jan 28, 2025 5:09 pm Post subject: speed up addgeometrycolumn |
|
|
Change postgis.sql to be:
| Code: | CREATE FUNCTION fix_geometry_columns() RETURNS text
AS
'
BEGIN
-- it would be better to find the correct schema name
UPDATE geometry_columns SET f_table_schema = ''public''
WHERE f_table_schema is NULL or f_table_schema NOT IN
( SELECT nspname::varchar FROM pg_namespace );
-- UPDATE geometry_columns SET attrelid = (
-- SELECT c.oid
-- FROM pg_class c, pg_attribute a, pg_namespace n
-- WHERE c.relname = geometry_columns.f_table_name::name
-- AND a.attrelid = c.oid AND c.relnamespace = n.oid
-- AND a.attname = geometry_columns.f_geometry_column::name
-- AND n.nspname = geometry_columns.f_table_schema::name
-- );
UPDATE geometry_columns SET varattnum = (
SELECT a.attnum
FROM pg_class c, pg_attribute a, pg_namespace n
WHERE n.nspname = geometry_columns.f_table_schema::name
AND c.relname = geometry_columns.f_table_name::name
AND a.attname = geometry_columns.f_geometry_column::name
AND a.attrelid = c.oid AND c.relnamespace = n.oid
);
RETURN ''geometry_columns table is now linked to the system tables'';
END;
'
LANGUAGE 'plpgsql' ; |
|
|
| Back to top |
|
|
cpurvis
Joined: 24 Feb 2025
Posts: 255
|
| Posted: Tue Apr 27, 2025 5:06 pm Post subject: |
|
|
Eureka! I found the spot that was holding up all the new and incoming geom tables:
in postgis.sql (at the end of the AddGeometryColumn function)
| Code: | --EXECUTE ''select fix_geometry_columns()'';
--SELECT fix_geometry_columns(); |
That was going through the ENTIRE geometry_columns table. Which is fine unless you're like me and buidling gobs of new tables in one session. So by pulling this out of the AddGeometryColumn, I *have* to do a
| Code: | | select fix_geometry_columns(); |
manually. |
|
| Back to top |
|
|
|
|
You can post new topics in this forum
You can reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum
You can attach files in this forum
You can download files in this forum
|
Powered by phpBB 2.0.4 © 2001, 2002 phpBB Group
|
FAQ
Memberlist
Usergroups
Register
Profile
Log in to check your private messages
Log in