--- path: docs/spatial-sql-book/02-where-your-sql-runs title: Where Your SQL Runs desc: Three connections, one search_path, and the five guards on every query image: /images/spatial-sql-book/02-where-your-sql-runs/cover.webp --- # Where Your SQL Runs *Three connections, one search_path, and the five guards on every query* ## What this lesson covers - What a connection actually is, and which three you can query - Why “relation does not exist” is usually not a typo - Your workspace holds your data — in five geometry types at once - The five guards, and why 100 rows back means nothing - Switch connection and the same question answers differently ## A connection is a database plus a search_path The reference book opens with twenty-four pages on Docker, `docker-postgis`, pgAdmin and `ogr2ogr`, because before you can write spatial SQL you have to own a database. There is nothing here to install — you already have three, and they are different in ways that matter. | Connection | What it is | What lives there | |---|---|---| | `map` | a remote PostGIS server, reached over a tunnel | OpenStreetMap: 41 tables, 440 million buildings, schema `osm` | | `workspace` | your own database | everything you import, plus reference sets like population and building footprints | | `sedona` | an engine, not a server | GeoParquet and CSV files queried by path — no tables at all | There is a fourth, `portal`. It holds the platform's own records — accounts, workspaces, billing. It is not lesson material and no query in this course touches it. The practical consequence: **the same SQL means different things on different connections.** `SELECT * FROM building_polygon` finds 440 million rows on `map` and nothing at all on `workspace`, and neither answer is a bug. #### What geometry can this connection even see? ```sql SELECT f_table_schema AS schema, f_table_name AS table_name, f_geometry_column AS geom_column, type AS geometry_type, srid FROM geometry_columns ORDER BY f_table_schema, f_table_name; ``` `geometry_columns` is a view every PostGIS database publishes: one row per geometry column it knows about, with its type and SRID. It is the fastest way to learn what a connection actually holds — run it on `workspace` and you get your own tables; the same query on `map` would list OpenStreetMap's forty-one. Neither result is more correct. *[Run this query yourself](https://app.gisowl.com/app/register?source=site)* ## Why "relation does not exist" is usually not a typo Postgres does not look for a table everywhere. It walks `search_path`, a list of schemas, and stops at the first match: ```sql SET search_path TO ws_acme, public; -- illustrative: the editor sets this for you SELECT * FROM buildings; -- finds ws_acme.buildings, never public.buildings ``` That single setting is the tenancy boundary in this product. Two workspaces run on one server, each with its own schema, and the connection decides which one the word `buildings` refers to. Nothing in your query changes — only what it resolves to. So when a table "does not exist", the useful question is not *did I spell it right* but **which schema was I asking**. `osm.*` tables only exist on `map`; `geo_*` tables only on `workspace`. Qualify the schema (`osm.building_polygon`) and the question disappears. #### What am I connected to? ```sql SELECT current_database() AS db, current_schema() AS schema, current_setting('search_path') AS search_path, PostGIS_Lib_Version() AS postgis, version() AS server; ``` Five settings that explain most confusion in a shared editor: which database, which schema, the full `search_path` Postgres will walk, the PostGIS version behind the spatial functions, and the server build. Run it first in any session you did not open yourself — it turns “that table does not exist” into “I am on the wrong connection”. *[Run this query yourself](https://app.gisowl.com/app/register?source=site)* #### The same editor, a different database ```sql SELECT table_name, (SELECT count(*) FROM information_schema.columns AS c WHERE c.table_schema = t.table_schema AND c.table_name = t.table_name) AS columns FROM information_schema.tables AS t WHERE table_schema = 'osm' ORDER BY table_name LIMIT 45; ``` This block runs on `map`, not `workspace` — look at the connection label above it. It is a different PostgreSQL server with a different schema, and the 41 `osm.*` tables listed here do not exist on the connection you were querying a moment ago. Nothing about your SQL changed; the answer to "what tables are there" did. *[Run this query yourself](https://app.gisowl.com/app/register?source=site)* #### Your own features — all five geometry types at once ```sql SELECT dataset_uuid, GeometryType(geom) AS geometry_type, geom FROM geo_dataset_features WHERE deleted_at IS NULL AND geom IS NOT NULL LIMIT 100; ``` `geo_dataset_features` is the workspace's feature store: everything you import lands here, from every dataset, in one table. Which means a single column holds points, lines and polygons together — legal in PostGIS, and a fact you have to design around. Note `deleted_at IS NULL`: rows are retired, not removed, so every query on this table needs that predicate or it counts ghosts. *[Run this query yourself](https://app.gisowl.com/app/register?source=site)* ## The five guards on every query Your SQL is not sent to the database as written. Before it runs it is parsed, checked and wrapped — and knowing exactly how saves you from blaming your query for the editor's rules: ```sql -- what actually reaches Postgres BEGIN; SET TRANSACTION READ ONLY; SET LOCAL statement_timeout = '30s'; SELECT to_jsonb(t) AS row FROM ( ) t LIMIT 101; COMMIT; ``` 1. **One statement.** Your SQL is parsed into a syntax tree and rejected unless it is a single `SELECT`. `WITH … SELECT` and `UNION ALL` still count as one. 2. **Read only.** The transaction cannot write, so `UPDATE`, `INSERT` and `CREATE` fail even if the guard somehow let them through. 3. **Thirty seconds.** Past that the statement is cancelled — which is what an unbounded query against 440 million rows looks like from the outside. 4. **A hundred rows.** It asks for 101 so it can tell you whether there were more. 5. **Wrapped as JSON.** `to_jsonb(t)` is why geometry comes back as GeoJSON rather than a hex blob, and why every column keeps its name. ### Why `EXPLAIN` does not work here ```sql EXPLAIN ANALYZE SELECT * FROM osm.building_polygon LIMIT 10; ``` That is an `ExplainStmt`, not a `SelectStmt`, so guard 1 rejects it before it reaches the database. The reference book leans on `EXPLAIN (ANALYZE, BUFFERS)` throughout; here you reason about plans from timings and row counts instead, which lesson 22 covers in full. The trade is deliberate. A shared editor that anyone can point at a 440-million-row table needs a ceiling more than it needs `EXPLAIN`. #### Ask for 500 rows ```sql SELECT generate_series(1, 500) AS n; ``` This query generates exactly 500 rows and there is no doubt about it — the count is written into the SQL. You will get 100. The editor asks the database for 101 so it can tell the difference between “that is all of them” and “there are more”, then shows you the first hundred. Nothing errors, nothing warns you in the result itself. *[Run this query yourself](https://app.gisowl.com/app/register?source=site)* ## Rules of thumb - A connection is a database plus a `search_path`. Same server, different path, different tables. - Every query runs READ ONLY, wrapped, capped at 100 rows, cancelled at 30 seconds. - Single SELECT only — the AST guard rejects everything else, `EXPLAIN` included. - A hundred rows back does not mean a hundred rows exist. --- **Run these queries against a live PostGIS.** [Open the lesson in the app](https://app.gisowl.com/app/register?source=site) — the same SQL, an editor, and your own data. ← [Prerequisites: Joins and Big O](/docs/spatial-sql-book/01-prerequisites) · [Geometry Is a Type, Not Two Numbers](/docs/spatial-sql-book/03-geometry-as-a-type) → · [All lessons](/docs/spatial-sql-book)