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, more than 81 polygons

Turkey has 81 provinces. Overture's divisions theme hands you more rows than that: Adana,
Antalya and Artvin each arrive twice, as separate geometries. 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 names.primary IS NOT NULL
)
GROUP BY region_name;

ST_Union_Agg merges the duplicates into one polygon per name. One line, and it removes a
whole class of double-counting bugs downstream.

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.

The data is open

Everything above is published under CC BY 4.0, province and sector breakdowns included:

• Report and tables: crmsolid.com/research/turkey-business-digital-report
• CSV, JSON and the SQL that produces the figures: github.com/CRM-Solid/turkey-business-digital-data
• Archived with a DOI: 10.5281/zenodo.22217770

If you are working with Overture in another country, I would genuinely like to know
whether the region field is as empty there as it is here.