<commit_before>
--------------------------------------------------------------------------------------
-- locality boundaries
--------------------------------------------------------------------------------------

DROP TABLE IF EXISTS admin_bdys.locality_bdys_analysis CASCADE;
CREATE TABLE admin_bdys.locality_bdys_analysis (
  gid SERIAL NOT NULL PRIMARY KEY,
  locality_pid character varying(15) NOT NULL,
  state character varying(3) NOT NULL,
  geom geometry(Polygon, 4283, 2) NOT NULL
) WITH (OIDS=FALSE);
ALTER TABLE admin_bdys.locality_bdys_analysis OWNER TO postgres;

INSERT INTO admin_bdys.locality_bdys_analysis (locality_pid, state, geom)
SELECT locality_pid,
       state, 
       ST_Subdivide((ST_Dump(ST_Buffer(geom, 0.0))).geom, 512)
  FROM admin_bdys.locality_bdys;

CREATE INDEX localities_analysis_geom_idx ON admin_bdys.locality_bdys_analysis USING gist(geom);
ALTER TABLE admin_bdys.locality_bdys_analysis CLUSTER ON localities_analysis_geom_idx;

ANALYZE admin_bdys.locality_bdys_analysis;


--------------------------------------------------------------------------------------
-- states
--------------------------------------------------------------------------------------

DROP TABLE IF EXISTS admin_bdys.state_bdys_analysis CASCADE;
CREATE TABLE admin_bdys.state_bdys_analysis (
  gid SERIAL NOT NULL PRIMARY KEY,
  state_pid character varying(15) NOT NULL,
  state character varying(3) NOT NULL,
  geom geometry(Polygon, 4283, 2) NOT NULL
) WITH (OIDS=FALSE);
ALTER TABLE admin_bdys.state_bdys_analysis OWNER TO postgres;

INSERT INTO admin_bdys.state_bdys_analysis (state_pid, state, geom)
SELECT state_pid,
       state,
       ST_Subdivide((ST_Dump(ST_Buffer(geom, 0.0))).geom, 512)
  FROM raw_admin_bdys.state_bdys;

CREATE INDEX states_analysis_geom_idx ON admin_bdys.state_bdys_analysis USING gist(geom);
ALTER TABLE admin_bdys.state_bdys_analysis CLUSTER ON states_analysis_geom_idx;

ANALYZE admin_bdys.state_bdys_analysis;


--------------------------------------------------------------------------------------
-- commonwealth electoral boundaries
--------------------------------------------------------------------------------------

DROP TABLE IF EXISTS admin_bdys.commonwealth_electorates_analysis CASCADE;
CREATE TABLE admin_bdys.commonwealth_electorates_analysis (
  gid SERIAL NOT NULL PRIMARY KEY,
  ce_pid character varying(15) NOT NULL,
  state character varying(3) NOT NULL,
  geom geometry(Polygon, 4283, 2) NOT NULL
) WITH (OIDS=FALSE);
ALTER TABLE admin_bdys.commonwealth_electorates_analysis OWNER TO postgres;

INSERT INTO admin_bdys.commonwealth_electorates_analysis (ce_pid, state, geom)
SELECT ce_pid,
       state,
       ST_Subdivide((ST_Dump(ST_Buffer(geom, 0.0))).geom, 512)
  FROM raw_admin_bdys.commonwealth_electorates;

CREATE INDEX commonwealth_electorates_analysis_geom_idx ON admin_bdys.commonwealth_electorates_analysis USING gist(geom);
ALTER TABLE admin_bdys.commonwealth_electorates_analysis CLUSTER ON commonwealth_electorates_analysis_geom_idx;

ANALYZE admin_bdys.commonwealth_electorates_analysis;


---------------------------------------------------------------------------------------------------
-- state electoral boundaries - choose bdys that will be current until at least 3 months from now
---------------------------------------------------------------------------------------------------

-- lower house
DROP TABLE IF EXISTS admin_bdys.state_lower_house_electorates_analysis CASCADE;
CREATE TABLE admin_bdys.state_lower_house_electorates_analysis (
  gid SERIAL NOT NULL PRIMARY KEY,
  se_pid character varying(15),
  state character varying(3),
  geom geometry(Polygon, 4283, 2)
) WITH (OIDS=FALSE);
ALTER TABLE admin_bdys.state_lower_house_electorates_analysis OWNER TO postgres;

INSERT INTO admin_bdys.state_lower_house_electorates_analysis (se_pid, state, geom)
SELECT se_pid,
       state,
       ST_Subdivide((ST_Dump(ST_Buffer(geom, 0.0))).geom, 512)
  FROM raw_admin_bdys.state_lower_house_electorates;

CREATE INDEX state_lower_house_electorates_analysis_geom_idx ON admin_bdys.state_lower_house_electorates_analysis USING gist(geom);
ALTER TABLE admin_bdys.state_lower_house_electorates_analysis CLUSTER ON state_lower_house_electorates_analysis_geom_idx;

ANALYZE admin_bdys.state_lower_house_electorates_analysis;


-- upper house
DROP TABLE IF EXISTS admin_bdys.state_upper_house_electorates_analysis CASCADE;
CREATE TABLE admin_bdys.state_upper_house_electorates_analysis (
  gid SERIAL NOT NULL PRIMARY KEY,
  se_pid character varying(15),
  state character varying(3),
  geom geometry(Polygon, 4283, 2)
) WITH (OIDS=FALSE);
ALTER TABLE admin_bdys.state_upper_house_electorates_analysis OWNER TO postgres;

INSERT INTO admin_bdys.state_upper_house_electorates_analysis (se_pid, state, geom)
SELECT se_pid,
       state,
       ST_Subdivide((ST_Dump(ST_Buffer(geom, 0.0))).geom, 512)
  FROM raw_admin_bdys.state_upper_house_electorates;

CREATE INDEX state_upper_house_electorates_analysis_geom_idx ON admin_bdys.state_upper_house_electorates_analysis USING gist(geom);
ALTER TABLE admin_bdys.state_upper_house_electorates_analysis CLUSTER ON state_upper_house_electorates_analysis_geom_idx;

ANALYZE admin_bdys.state_upper_house_electorates_analysis;


--------------------------------------------------------------------------------------
-- local government areas
--------------------------------------------------------------------------------------

DROP TABLE IF EXISTS admin_bdys.local_government_areas_analysis CASCADE;
CREATE TABLE admin_bdys.local_government_areas_analysis (
  gid SERIAL NOT NULL PRIMARY KEY,
  lga_pid character varying(15),
  state character varying(3),
  geom geometry(Polygon, 4283, 2)
) WITH (OIDS=FALSE);
ALTER TABLE admin_bdys.local_government_areas_analysis OWNER TO postgres;

INSERT INTO admin_bdys.local_government_areas_analysis (lga_pid, state, geom)
SELECT lga_pid,
       state,
       ST_Subdivide((ST_Dump(ST_Buffer(geom, 0.0))).geom, 512)
  FROM raw_admin_bdys.local_government_areas;

CREATE INDEX local_government_areas_analysis_geom_idx ON admin_bdys.local_government_areas_analysis USING gist(geom);
ALTER TABLE admin_bdys.local_government_areas_analysis CLUSTER ON local_government_areas_analysis_geom_idx;

ANALYZE admin_bdys.local_government_areas_analysis;


--------------------------------------------------------------------------------------
-- local government wards
--------------------------------------------------------------------------------------

DROP TABLE IF EXISTS admin_bdys.local_government_wards_analysis CASCADE;
CREATE TABLE admin_bdys.local_government_wards_analysis (
  gid SERIAL NOT NULL PRIMARY KEY,
  ward_pid character varying(15),
  state character varying(3),
  geom geometry(Polygon, 4283, 2)
) WITH (OIDS=FALSE);
ALTER TABLE admin_bdys.local_government_wards_analysis OWNER TO postgres;

INSERT INTO admin_bdys.local_government_wards_analysis (ward_pid, state, geom)
SELECT ward_pid,
       state,
       ST_Subdivide((ST_Dump(ST_Buffer(geom, 0.0))).geom, 512)
  FROM raw_admin_bdys.local_government_wards;

CREATE INDEX local_government_wards_analysis_geom_idx ON admin_bdys.local_government_wards_analysis USING gist(geom);
ALTER TABLE admin_bdys.local_government_wards_analysis CLUSTER ON local_government_wards_analysis_geom_idx;

ANALYZE admin_bdys.local_government_wards_analysis;




--TO DO:
--  - Do the above for all admin bdy types


<commit_msg>renamed index<commit_after>
--------------------------------------------------------------------------------------
-- locality boundaries
--------------------------------------------------------------------------------------

DROP TABLE IF EXISTS admin_bdys.locality_bdys_analysis CASCADE;
CREATE TABLE admin_bdys.locality_bdys_analysis (
  gid SERIAL NOT NULL PRIMARY KEY,
  locality_pid character varying(16) NOT NULL,
  state character varying(3) NOT NULL,
  geom geometry(Polygon, 4283, 2) NOT NULL
) WITH (OIDS=FALSE);
ALTER TABLE admin_bdys.locality_bdys_analysis OWNER TO postgres;

INSERT INTO admin_bdys.locality_bdys_analysis (locality_pid, state, geom)
SELECT locality_pid,
       state, 
       ST_Subdivide((ST_Dump(ST_Buffer(geom, 0.0))).geom, 512)
  FROM admin_bdys.locality_bdys;

CREATE INDEX locality_bdys_analysis_geom_idx ON admin_bdys.locality_bdys_analysis USING gist(geom);
ALTER TABLE admin_bdys.locality_bdys_analysis CLUSTER ON locality_bdys_analysis_geom_idx;

ANALYZE admin_bdys.locality_bdys_analysis;


--------------------------------------------------------------------------------------
-- states
--------------------------------------------------------------------------------------

DROP TABLE IF EXISTS admin_bdys.state_bdys_analysis CASCADE;
CREATE TABLE admin_bdys.state_bdys_analysis (
  gid SERIAL NOT NULL PRIMARY KEY,
  state_pid character varying(15) NOT NULL,
  state character varying(3) NOT NULL,
  geom geometry(Polygon, 4283, 2) NOT NULL
) WITH (OIDS=FALSE);
ALTER TABLE admin_bdys.state_bdys_analysis OWNER TO postgres;

INSERT INTO admin_bdys.state_bdys_analysis (state_pid, state, geom)
SELECT state_pid,
       state,
       ST_Subdivide((ST_Dump(ST_Buffer(geom, 0.0))).geom, 512)
  FROM raw_admin_bdys.state_bdys;

CREATE INDEX states_analysis_geom_idx ON admin_bdys.state_bdys_analysis USING gist(geom);
ALTER TABLE admin_bdys.state_bdys_analysis CLUSTER ON states_analysis_geom_idx;

ANALYZE admin_bdys.state_bdys_analysis;


--------------------------------------------------------------------------------------
-- commonwealth electoral boundaries
--------------------------------------------------------------------------------------

DROP TABLE IF EXISTS admin_bdys.commonwealth_electorates_analysis CASCADE;
CREATE TABLE admin_bdys.commonwealth_electorates_analysis (
  gid SERIAL NOT NULL PRIMARY KEY,
  ce_pid character varying(15) NOT NULL,
  state character varying(3) NOT NULL,
  geom geometry(Polygon, 4283, 2) NOT NULL
) WITH (OIDS=FALSE);
ALTER TABLE admin_bdys.commonwealth_electorates_analysis OWNER TO postgres;

INSERT INTO admin_bdys.commonwealth_electorates_analysis (ce_pid, state, geom)
SELECT ce_pid,
       state,
       ST_Subdivide((ST_Dump(ST_Buffer(geom, 0.0))).geom, 512)
  FROM raw_admin_bdys.commonwealth_electorates;

CREATE INDEX commonwealth_electorates_analysis_geom_idx ON admin_bdys.commonwealth_electorates_analysis USING gist(geom);
ALTER TABLE admin_bdys.commonwealth_electorates_analysis CLUSTER ON commonwealth_electorates_analysis_geom_idx;

ANALYZE admin_bdys.commonwealth_electorates_analysis;


--------------------------------------------------------------------------------------
-- state electoral boundaries
--------------------------------------------------------------------------------------

-- lower house
DROP TABLE IF EXISTS admin_bdys.state_lower_house_electorates_analysis CASCADE;
CREATE TABLE admin_bdys.state_lower_house_electorates_analysis (
  gid SERIAL NOT NULL PRIMARY KEY,
  se_pid character varying(15),
  state character varying(3),
  geom geometry(Polygon, 4283, 2)
) WITH (OIDS=FALSE);
ALTER TABLE admin_bdys.state_lower_house_electorates_analysis OWNER TO postgres;

INSERT INTO admin_bdys.state_lower_house_electorates_analysis (se_pid, state, geom)
SELECT se_pid,
       state,
       ST_Subdivide((ST_Dump(ST_Buffer(geom, 0.0))).geom, 512)
  FROM raw_admin_bdys.state_lower_house_electorates;

CREATE INDEX state_lower_house_electorates_analysis_geom_idx ON admin_bdys.state_lower_house_electorates_analysis USING gist(geom);
ALTER TABLE admin_bdys.state_lower_house_electorates_analysis CLUSTER ON state_lower_house_electorates_analysis_geom_idx;

ANALYZE admin_bdys.state_lower_house_electorates_analysis;


-- upper house
DROP TABLE IF EXISTS admin_bdys.state_upper_house_electorates_analysis CASCADE;
CREATE TABLE admin_bdys.state_upper_house_electorates_analysis (
  gid SERIAL NOT NULL PRIMARY KEY,
  se_pid character varying(15),
  state character varying(3),
  geom geometry(Polygon, 4283, 2)
) WITH (OIDS=FALSE);
ALTER TABLE admin_bdys.state_upper_house_electorates_analysis OWNER TO postgres;

INSERT INTO admin_bdys.state_upper_house_electorates_analysis (se_pid, state, geom)
SELECT se_pid,
       state,
       ST_Subdivide((ST_Dump(ST_Buffer(geom, 0.0))).geom, 512)
  FROM raw_admin_bdys.state_upper_house_electorates;

CREATE INDEX state_upper_house_electorates_analysis_geom_idx ON admin_bdys.state_upper_house_electorates_analysis USING gist(geom);
ALTER TABLE admin_bdys.state_upper_house_electorates_analysis CLUSTER ON state_upper_house_electorates_analysis_geom_idx;

ANALYZE admin_bdys.state_upper_house_electorates_analysis;


--------------------------------------------------------------------------------------
-- local government areas
--------------------------------------------------------------------------------------

DROP TABLE IF EXISTS admin_bdys.local_government_areas_analysis CASCADE;
CREATE TABLE admin_bdys.local_government_areas_analysis (
  gid SERIAL NOT NULL PRIMARY KEY,
  lga_pid character varying(15),
  state character varying(3),
  geom geometry(Polygon, 4283, 2)
) WITH (OIDS=FALSE);
ALTER TABLE admin_bdys.local_government_areas_analysis OWNER TO postgres;

INSERT INTO admin_bdys.local_government_areas_analysis (lga_pid, state, geom)
SELECT lga_pid,
       state,
       ST_Subdivide((ST_Dump(ST_Buffer(geom, 0.0))).geom, 512)
  FROM raw_admin_bdys.local_government_areas;

CREATE INDEX local_government_areas_analysis_geom_idx ON admin_bdys.local_government_areas_analysis USING gist(geom);
ALTER TABLE admin_bdys.local_government_areas_analysis CLUSTER ON local_government_areas_analysis_geom_idx;

ANALYZE admin_bdys.local_government_areas_analysis;


--------------------------------------------------------------------------------------
-- local government wards
--------------------------------------------------------------------------------------

DROP TABLE IF EXISTS admin_bdys.local_government_wards_analysis CASCADE;
CREATE TABLE admin_bdys.local_government_wards_analysis (
  gid SERIAL NOT NULL PRIMARY KEY,
  ward_pid character varying(15),
  state character varying(3),
  geom geometry(Polygon, 4283, 2)
) WITH (OIDS=FALSE);
ALTER TABLE admin_bdys.local_government_wards_analysis OWNER TO postgres;

INSERT INTO admin_bdys.local_government_wards_analysis (ward_pid, state, geom)
SELECT ward_pid,
       state,
       ST_Subdivide((ST_Dump(ST_Buffer(geom, 0.0))).geom, 512)
  FROM raw_admin_bdys.local_government_wards;

CREATE INDEX local_government_wards_analysis_geom_idx ON admin_bdys.local_government_wards_analysis USING gist(geom);
ALTER TABLE admin_bdys.local_government_wards_analysis CLUSTER ON local_government_wards_analysis_geom_idx;

ANALYZE admin_bdys.local_government_wards_analysis;




--TO DO:
--  - Do the above for all admin bdy types



{'fraction_non_alphanumeric': 0.19287934062964465, 'fraction_numerical': 0.012363194162950952, 'mean_word_length': 2.9422103861517974, 'pattern_counts': {'":': 0, '<': 3, '<?xml version=': 0, '>': 3, 'https://': 0, 'lorem ipsum': 0, 'www.': 0, 'xml': 0}, 'pii_count': 0, 'substrings_counts': 0, 'word_list_counts': {'cursed_substrings.json': 0, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'dir': 'git-commits-cleaned', 'id': '6612321', 'n_tokens_mistral': 4710, 'n_tokens_neox': 4379, 'n_words': 978}