Claude VM
a6c03a091e
Moved from gov-agreg/src/pages/achizitii/* to root (drop prefix). - 22 pages migrated, 127 files total - All internal links: /achizitii/X → /X (176 occurrences fixed) - AchizitiiLayout subnav rewritten: /X paths, top-right link to vreaudigital.ro hub - BaseLayout new (vreau.digital branding, OG tags, site URL) - astro.config.mjs: site https://vreau.digital, server output (was static) - docker-compose: port 5096 (vreaudigital is 5095), container vreau-digital - deploy.sh: paths /opt/vreau-digital, log /var/log/vreau-digital-deploy.log Backend shared with gov-agreg: - PostgreSQL satra (same schemas: seap, firms, anaf, anre, ...) - Photon, Martin tiles - Infisical /vreaudigital path (DATABASE_URL etc. shared) build: PASS (npx astro check 0 errors, npm run build 5s vite + 10s server)
47 lines
1.5 KiB
SQL
47 lines
1.5 KiB
SQL
-- 014_firms_postal_codes.sql
|
|
-- GeoNames RO postal codes (37915 entries, CC-BY 4.0).
|
|
-- Used for fast batch geocoding of firms.entities at postal-code precision
|
|
-- — covers ~2.07M firms (52%) with adr_cod_postal populated.
|
|
-- Source: https://download.geonames.org/export/zip/RO.zip
|
|
-- Refresh: yearly via cron (data updates ~yearly per GeoNames).
|
|
|
|
CREATE TABLE IF NOT EXISTS firms.postal_codes (
|
|
postal_code text NOT NULL,
|
|
place_name text NOT NULL,
|
|
county text,
|
|
county_code text,
|
|
admin2_code text,
|
|
admin3_code text,
|
|
admin3_name text,
|
|
lat numeric(9,6) NOT NULL,
|
|
lng numeric(9,6) NOT NULL,
|
|
accuracy int,
|
|
PRIMARY KEY (postal_code, place_name)
|
|
);
|
|
|
|
-- One row per postal code — when multiple places share a code, pick the one
|
|
-- with the best accuracy (lowest int value in GeoNames is most precise).
|
|
CREATE OR REPLACE VIEW firms.postal_codes_best AS
|
|
SELECT DISTINCT ON (postal_code)
|
|
postal_code, place_name, county, county_code, lat, lng, accuracy
|
|
FROM firms.postal_codes
|
|
ORDER BY postal_code, accuracy NULLS LAST, place_name;
|
|
|
|
CREATE INDEX IF NOT EXISTS idx_postal_codes_postal ON firms.postal_codes(postal_code);
|
|
|
|
-- Staging table for COPY from the GeoNames TSV layout.
|
|
CREATE TABLE IF NOT EXISTS firms.staging_postal_codes (
|
|
country_code text,
|
|
postal_code text,
|
|
place_name text,
|
|
admin1_name text,
|
|
admin1_code text,
|
|
admin2_name text,
|
|
admin2_code text,
|
|
admin3_name text,
|
|
admin3_code text,
|
|
lat text,
|
|
lng text,
|
|
accuracy text
|
|
);
|