Four traps in querying 1.79M Overture Maps records with DuckDB

We needed a business directory for Turkey inside our CRM, so we went to
Overture Maps places: open, CDLA-Permissive, no API key,
no quota, ~1.8M Turkish records sitting in public GeoParquet on S3.

Reading it turned out to be the easy part. Here are the four things that actually cost us
time, and the numbers that came out the other end.

1. A join against remote parquet does not stream

The obvious query is one statement: read the places theme, read the divisions theme, keep
the rows whose point falls inside a Turkish province.

-- Do not do this against S3.
SELECT p.*
FROM read_parquet('s3://.../theme=places/*/*', hive_partitioning=1) p
JOIN regions r ON ST_Within(p.geometry, r.geom)
WHERE r.country = 'TR';

DuckDB materialises that join before it emits a single row. Ours sat at 2.5 GB of RAM
and produced nothing after fifteen minutes, with no way to tell whether it was making
progress.

The fix is boring and works: two stages. Stage one pulls the country down with a plain
bounding box and zero spatial work.

CREATE TEMP TABLE places_tr AS
SELECT id,
       names.primary        AS name,
       categories.primary   AS category,
       websites[1]          AS website,
       bbox.ymin            AS lat,
       bbox.xmin            AS lon,
       geometry             AS geom
FROM read_parquet('s3://.../theme=places/*/*', hive_partitioning=1)
WHERE bbox.xmin BETWEEN 25.5 AND 45.0
  AND bbox.ymin BETWEEN 35.7 AND 42.3
  AND names.primary IS NOT NULL
  AND (operating_status IS NULL OR operating_status <> 'closed');

A rectangle over Turkey also sweeps in slivers of Greece, Bulgaria and Georgia. That is
fine, stage two throws them out. Stage two then loops province by province, so each join
touches one polygon and a few thousand candidate rows:

SELECT p.* FROM places_tr p, tr_regions r
WHERE r.region_name = 'İzmir' AND ST_Within(p.geom, r.geom);

Every step is bounded and you can watch it progress. Same result, and the whole run
finishes instead of hanging.

2. 81 provinces, 109 polygons

Turkey has 81 provinces. Overture's divisions theme hands you 109 region rows for the
country. My first reading was "duplicates": Adana, Antalya and Artvin each arrive twice, so
merge by name and move on. That works, but it hides the real reason, which is in the schema:
every coastal province has a second division_area row with class = 'maritime' for its
territorial waters (81 land + 28 maritime in release 2026-07-22.0; Antalya's maritime
polygon is larger than its land). If you loop over the raw rows you process those provinces
(and their companies) twice.

CREATE TEMP TABLE tr_regions AS
SELECT region_name, ST_Union_Agg(geom) AS geom
FROM (
  SELECT names.primary AS region_name, geometry AS geom
  FROM read_parquet('.../theme=divisions/*/*', hive_partitioning=1)
  WHERE country = 'TR' AND subtype = 'region' AND class = 'land'
    AND names.primary IS NOT NULL
)
GROUP BY region_name;

class = 'land' is the fix; the GROUP BY stays as a guard so downstream code can rely on
exactly one polygon per province name. One line, and it removes a whole class of
double-counting bugs.

3. The address field will not tell you the province

This is the one worth knowing before you plan anything. Overture's address region field
is null on roughly 92% of Turkish records, and inconsistent on the rest. There is no
salvaging it. Province has to be assigned geographically, from coordinates.

And it has to be a real point-in-polygon test, not a bounding box. Province bounding boxes
overlap heavily; picking the smallest containing box puts central İzmir in Manisa and
central Antalya in Burdur. We found that the entertaining way.

4. Lone surrogates will abort your bulk insert

Loading the results into Postgres with Npgsql's binary COPY, the batch died on real
company names. The cause: lone surrogates, half of an emoji pair, usually from a
truncated source record. Npgsql's UTF-8 encoder throws on those and takes the whole batch
with it.

Two details that cost us a second round trip:

Truncating a string to a column width can split a valid surrogate pair, so sanitising has to run after truncation, not before.
Null bytes need dropping outright; Postgres rejects them in text columns.

What it bought

Querying Overture's S3 parquet directly from a Turkish host: 277 seconds for a single
province. The same query against the local Postgres catalogue: 15 ms. That is the
difference between a feature and a batch job.

And then the actual finding

With 1,786,700 businesses loaded, we asked how many of them have a website. The field says
684,496, or 38.3%.

So we requested every one of those addresses. 413,777 answered. The rest time out,
refuse the connection, or sit on an expired domain.

The honest national figure is 23.2%: roughly one Turkish business in four can be
reached on the web at all. Meanwhile 542,323 businesses publish a phone number and no
website whatsoever.

The same gap shows up in the socials array, in the other direction: Overture has Facebook
on 95.3% of Turkish records but Instagram on only ~14k, an artefact of when the source
listings were assembled. Crawling the company websites we could reach found Instagram on
about 36% of them, taking the count to 232,928. Treat a populated field as a claim, not
a measurement.