This page needs a newer browser to run its tools. You can still read everything here.

GIS & Geospatial Data (QGIS, GeoPandas, PostGIS) Module 8

Spatial SQL with PostGIS interview questions

Interview questions on spatial SQL with PostGIS: short warm-ups, then what each harder question tests, the first step of a good answer and its follow-ups.

19 questions: 5 warm-ups and 14 with model answers · Levels: Fresher to staff

Warm-up questions

Short questions with short answers, each answered in full.

Fresher

What is an SRID in PostGIS, and what happens when one query compares geometries with different SRIDs?

Answered in the lesson PostGIS setup: geometry, geography and SRIDs
Fresher

ST_Contains or ST_Covers for points in polygons: what is the difference, and when does it matter?

Answered in the lesson Spatial relationships and joins in SQL
Mid

In a PostGIS plan, what is the difference between an Index Cond and a Filter?

Answered in the lesson PostGIS indexes, KNN and query plans
Fresher

Why is ST_Buffer(geom, 0) a risky way to repair an invalid polygon?

Answered in the lesson Geometry processing in PostGIS
Fresher

Why must GeoJSON for a web map be in longitude and latitude?

Answered in the lesson Serving GeoJSON, vector tiles and OGC APIs

Questions with model answers

What each question tests, the first step of a good answer and the follow-ups to expect.

Fresher to mid

A nightly job loads a partner's CSV of clinics into a geometry(Point, 32643) column. Since the partner changed its export, 40 of 9,000 clinics appear on the equator near 70.5° E, and the job logged no error. What happened, how do you find every bad row, and how do you stop it in the database?
What it tests

Knowing that a typed column gives a value without an SRID its own, telling degrees from metres by their range, and enforcing a rule with a constraint.

First step

Read the stored numbers of one bad row. Values like 75.06 and 18.13 are degrees, not metres of UTM zone 43N, where eastings near the town are about 500,000. Then ask how they got in without an error: a plain POINT(lon lat) with no SRID written takes the column's SRID, so the degrees were stored as metres.
Follow-ups
- How do you repair the 40 rows without loading everything again?
- Which constraint stops the next bad export, and what does it cost on each insert?
- The partner's next file mixes both kinds of row. How do you load it safely?
From the lesson PostGIS setup: geometry, geography and SRIDs
Model answer, Pro. Model answers open with any pass; prices are in the notice on this page.
Opening the model answers with your pass…
Model answers open with any MySmartCoPilot pass.
Pro pass · 1 week₹79$2.99
30 days₹179$5.99
1 year₹1,699$45
One payment; a pass never renews. The price shown is the total you pay: no fees or charges are added at checkout.
Passes can’t be bought right now, so the model answers can’t be opened at the moment. If you have a pass, enter your licence key.
Choose a pass I have a licence key
It also opens every other in-depth part in Learn and every Pro tool. Sales are final, except where the law gives you a right to withdraw, for example in the EU and the UK (refund policy).
In the answer:
- Model answer
- Follow-up answers
- Common mistakes
- Rubric
- Variants
- about 3 minutes
Mid to senior

You must store 2 million bus stops for a country six UTM zones wide. The main queries are "stops within 500 m of me" and a nightly length of every route. Do you choose geometry or geography, in which SRID, and what changes if the team later needs buffers and unions every day?
What it tests

Choosing a type from the extent and the operations, knowing what each type measures and which functions it has, and keeping one stored truth with casts where needed.

First step

Start from the extent: six zones means no single UTM plane is accurate everywhere; in the lesson's run one zone's plane was 3,240 m too long over 952 km, 9 degrees east of its centre. Then list the operations: radius searches and lengths are among geography's 31 functions, and both are true in metres on the ellipsoid.
Follow-ups
- How do you index the radius search, and does the index work on geography?
- Where would a national projected system such as EPSG:7755 fit?
- How do you serve web maps from the same table?
From the lesson PostGIS setup: geometry, geography and SRIDs
Model answer, Pro.
In the answer:
- Model answer
- Follow-up answers
- Common mistakes
- Rubric
- Variants
- about 3 minutes
Fresher to mid

A dashboard sums clinics per ward from a join with ST_Intersects and shows 9 clinics for a town that has 8. Explain the cause, write a query that counts every clinic exactly once, and say how you would make sure the totals still add up after every data load.
What it tests

Seeing what intersects does with points on borders, turning a business decision into SQL, and guarding it with a query that fails loudly.

First step

Find the clinic that is counted twice: join clinics to wards, group by clinic and keep those with more than one ward. Then look at its coordinates against the wards' borders, and ask which predicates count a point that lies exactly on a boundary.
Follow-ups
- Why not simply switch the join to ST_Contains?
- Who decides which ward a border clinic belongs to, and where should that decision live?
- How would you test the rule automatically after each load?
From the lesson Spatial relationships and joins in SQL
Model answer, Pro.
In the answer:
- Model answer
- Follow-up answers
- Common mistakes
- Rubric
- Variants
- about 3 minutes
Mid to senior

A grant rule says two wards are neighbours only if they share a stretch of border, not just a corner. An analyst used ST_Touches and got more neighbour pairs than the planners expect for a grid of 12 square wards. Which query gives the right pairs, and how do you explain the difference to the analyst?
What it tests

Knowing what a named predicate is true for, reading the DE-9IM matrix behind it, and writing a pattern when no named predicate fits the rule.

First step

Ask what ST_Touches is true for: any pair whose boundaries meet while their interiors do not. Then sketch where two squares of a grid can meet, along a side or at a single corner, and look for the test that tells a shared line from a shared point.
Follow-ups
- How many pairs should the grid give, and how do you check the number without a database?
- What changes for real ward boundaries, which are not squares?
- Can the pattern query use a spatial index?
From the lesson Spatial relationships and joins in SQL
Model answer, Pro.
In the answer:
- Model answer
- Follow-up answers
- Common mistakes
- Rubric
- Variants
- about 3 minutes
Mid to senior

A report lists each household's nearest clinic within 1.5 km with a CROSS JOIN LATERAL query. It has fewer rows than there are households, and the team published it as complete. What happened, how do you fix the query, and what should the report say?
What it tests

Knowing what a lateral subquery with no result does to its outer row in each kind of join, and reporting the rows without an answer instead of losing them.

First step

Count the households, then the report's rows, and look at one missing household: measure its distance to every clinic. Then ask what CROSS JOIN LATERAL does with an outer row when the subquery, limited to 1.5 km, returns nothing.
Follow-ups
- Two clinics are exactly as near to one household. What does the query do, and what should it do?
- How do you check the report's numbers by a second method?
- The household table grows to 2 million rows. What happens to the query's cost?
From the lesson Spatial relationships and joins in SQL
Model answer, Pro.
In the answer:
- Model answer
- Follow-up answers
- Common mistakes
- Rubric
- Variants
- about 3 minutes
Mid to senior

A "five nearest stores" query takes 300 ms on 2 million stores, although stores.geom has a GiST index. The SQL joins the customer's row to the stores and orders by distance. What do you look for in the plan, and how do you rewrite the query?
What it tests

Knowing when the distance operator is index-assisted, telling a Sort from an index scan in distance order, and writing nearest-first search per row with LATERAL.

First step

Run EXPLAIN ANALYZE and look for a Sort above a Seq Scan of stores: that means every store was measured and the two million distances sorted. Then ask what the index needs before it can return stores in distance order, and whether this query's ORDER BY gives it that.
Follow-ups
- What changes when the customer's location arrives as a query parameter instead of a row?
- The app wants the five nearest open stores. How do you add the filter without losing the index order?
- How do you return distances in metres when the column is geometry in SRID 4326?
From the lesson PostGIS indexes, KNN and query plans
Model answer, Pro.
In the answer:
- Model answer
- Follow-up answers
- Common mistakes
- Rubric
- Variants
- about 2 minutes
Senior to staff

A fleet table takes 50 million GPS pings a day, appended in time order and never updated. Dashboards ask for the pings in one district during the last hour. Which indexes do you build, and why not a single GiST index on the geometry?
What it tests

Matching index methods to how rows are stored and queried, and combining a time filter with a spatial one at this scale.

First step

List the two filters of every dashboard query, time and place, and how the rows lie on disk: in time order, not in spatial order. Then ask which filter a BRIN index could use, and what one GiST index over the whole table would cost on 50 million inserts a day.
Follow-ups
- How does partitioning the table by day change the answer?
- When would a BRIN index on the geometry pay off for such a table?
- What would you measure before choosing?
From the lesson PostGIS indexes, KNN and query plans
Model answer, Pro.
In the answer:
- Model answer
- Follow-up answers
- Common mistakes
- Rubric
- Variants
- about 2 minutes
Mid to senior

A radius endpoint answered in 3 ms for months. After a full nightly reload of its table it takes 40 ms, although the index exists and EXPLAIN still shows a Bitmap Index Scan. What do you check, and what fixes it for good?
What it tests

Reading the pages and rows a plan reports, knowing how row order and statistics affect an indexed query, and making the fix part of the load.

First step

Compare the plan of the slow query with one from before the reload, line by line: the same index, the same candidates, but perhaps many more Heap Blocks, because the reload wrote the rows in a different order. Check too when the table was last analysed.
Follow-ups
- How do you make the fast layout survive the next reload?
- Would BRIN be a better index after the fix?
- What would you monitor so the next regression is caught before users notice it?
From the lesson PostGIS indexes, KNN and query plans
Model answer, Pro.
In the answer:
- Model answer
- Follow-up answers
- Common mistakes
- Rubric
- Variants
- about 2 minutes
Mid to senior

A state's 30 district boundaries are simplified nightly with ST_SimplifyPreserveTopology for a dashboard. Every district is valid, yet analysts find slivers between neighbours and households counted in two districts. Explain why, and how you would produce simplified districts that still add up.
What it tests

Understanding coverages and shared edges, knowing what each simplification function promises, and separating display geometry from the geometry that is counted.

First step

Ask where each shared border is stored: once in each of the two districts. Then ask what a function that sees one district at a time can know about its neighbour's copy of the same border, and look for slivers along exactly those borders.
Follow-ups
- How do you check, in SQL, that the input boundaries form a valid coverage before simplifying?
- The dashboard only draws the districts. Why does the count still go wrong?
- What would you keep in the database for analysts, and what for the map?
From the lesson Geometry processing in PostGIS
Model answer, Pro.
In the answer:
- Model answer
- Follow-up answers
- Common mistakes
- Rubric
- Variants
- about 2 minutes
Mid to senior

A city wants "hot spots" of dengue cases from 40,000 geocoded cases. DBSCAN with eps 50 m gives 2,000 tiny clusters; with eps 500 m, one cluster covers half the city. How do you choose eps and minpoints, and what do you report so that the result can be trusted?
What it tests

Knowing what eps and minpoints mean, tying parameters to the public-health question, and reporting clustering results with their sensitivity.

First step

Start from the question, not the algorithm: what distance makes two cases related, for example how far the mosquito flies or a household's neighbourhood, and how many cases make a focus worth a visit. Then run DBSCAN at a few values around those and look at how the clusters and the noise change.
Follow-ups
- Why do border cases sometimes change cluster between runs, and how do you stop it?
- Cases are dense where people are dense. How do you keep the hot spots from simply mapping population?
- When would k-means be the better tool for this team?
From the lesson Geometry processing in PostGIS
Model answer, Pro.
In the answer:
- Model answer
- Follow-up answers
- Common mistakes
- Rubric
- Variants
- about 2 minutes
Senior to staff

A team generalises the national boundary layer inside PostGIS every night for web maps, and analysts now complain that areas and counts changed. Which processing belongs in the database, and how do you keep analytical and display geometry apart without two teams editing two copies?
What it tests

Deciding where processing belongs, designing derived geometry that is rebuilt from one source, and protecting measurements from display shortcuts.

First step

Check which geometry the analysts' queries read: if the nightly job overwrote the source column with simplified shapes, every area and count since then used display geometry. The first fix is to keep the source untouched and put the simplified shapes somewhere else.
Follow-ups
- Where should the simplified geometry live, and how is it kept in step with the source?
- Which steps of the nightly job would you move to Python or QGIS, if any?
- How do you prove to the analysts that their numbers are correct again?
From the lesson Geometry processing in PostGIS
Model answer, Pro.
In the answer:
- Model answer
- Follow-up answers
- Common mistakes
- Rubric
- Variants
- about 2 minutes
Mid to senior

A city map serves 5 million address points as vector tiles straight from PostGIS. Tiles at low zoom weigh over a megabyte and the map stutters while panning. What do you measure first, and what do you change in the tile SQL?
What it tests

Measuring tile sizes per zoom, knowing why dense points make heavy tiles, and choosing between dropping, thinning and aggregating features by zoom.

First step

Measure before changing anything: for a few of the busiest tiles at each zoom, count the features and the bytes, with the same SQL the server runs. Then compare the zooms with each other, and the features per tile with what a screen can show.
Follow-ups
- Which zoom would you start showing single addresses at, and how do you decide?
- What would you show at the lower zooms instead?
- Would pre-built tiles be better than tiles made on each request here?
From the lesson Serving GeoJSON, vector tiles and OGC APIs
Model answer, Pro.
In the answer:
- Model answer
- Follow-up answers
- Common mistakes
- Rubric
- Variants
- about 2 minutes
Mid to senior

You must put a "clinics near me" endpoint backed by PostGIS on the public internet. What sits between the internet and the database, and which settings of the database and its roles do you change before launch?
What it tests

Designing least-privilege access to a spatial database, limiting what one request can cost, and keeping the database itself off the public network.

First step

Draw the path of one request: browser, web server or API, database. Then give each hop the least it needs: the public reaches only the API, the API connects with a role that can read the clinics and nothing else, and every value from the request reaches the SQL as a parameter.
Follow-ups
- How do you stop one expensive request from tying up the database?
- How do you show that the endpoint cannot be used to change data?
- What would you log, and what must you never log?
From the lesson Serving GeoJSON, vector tiles and OGC APIs
Model answer, Pro.
In the answer:
- Model answer
- Follow-up answers
- Common mistakes
- Rubric
- Variants
- about 2 minutes
Fresher to mid

A dashboard downloads 200,000 land parcels as one GeoJSON file of 90 MB before it draws anything. Users also want to click a parcel to see its details and to download a district's parcels. GeoJSON, vector tiles or OGC API - Features: which do you use for what?
What it tests

Matching formats and services to how clients use the data, and estimating the cost of each choice from sizes.

First step

Separate the three uses: looking at the map, clicking one parcel, downloading a district. Then ask what each needs to receive: only the parcels on screen, one parcel's attributes, or a complete set of features in a known format.
Follow-ups
- How would you check that tiles really lighten the first screen?
- Where do the parcel details come from when a user clicks on a tile?
- What does the download endpoint need to protect the database?
From the lesson Serving GeoJSON, vector tiles and OGC APIs
Model answer, Pro.
In the answer:
- Model answer
- Follow-up answers
- Common mistakes
- Rubric
- Variants
- about 2 minutes

The lessons of this module

PostGIS setup: geometry, geography and SRIDs Free lesson, Pro answers: 15 minutes
Spatial relationships and joins in SQL Free core, Pro depth: 6 of 15 minutes free
PostGIS indexes, KNN and query plans Free core, Pro depth: 6 of 15 minutes free
Geometry processing in PostGIS Free core, Pro depth: 6 of 15 minutes free
Serving GeoJSON, vector tiles and OGC APIs Free core, Pro depth: 6 of 15 minutes free

All GIS & Geospatial Data (QGIS, GeoPandas, PostGIS) interview questions · The GIS & Geospatial Data (QGIS, GeoPandas, PostGIS) track

Warm-up questions

What is an SRID in PostGIS, and what happens when one query compares geometries with different SRIDs?

ST_Contains or ST_Covers for points in polygons: what is the difference, and when does it matter?

In a PostGIS plan, what is the difference between an Index Cond and a Filter?

Why is ST_Buffer(geom, 0) a risky way to repair an invalid polygon?

Why must GeoJSON for a web map be in longitude and latitude?

Questions with model answers

A nightly job loads a partner's CSV of clinics into a geometry(Point, 32643) column. Since the partner changed its export, 40 of 9,000 clinics appear on the equator near 70.5° E, and the job logged no error. What happened, how do you find every bad row, and how do you stop it in the database?

You must store 2 million bus stops for a country six UTM zones wide. The main queries are "stops within 500 m of me" and a nightly length of every route. Do you choose geometry or geography, in which SRID, and what changes if the team later needs buffers and unions every day?

A dashboard sums clinics per ward from a join with ST_Intersects and shows 9 clinics for a town that has 8. Explain the cause, write a query that counts every clinic exactly once, and say how you would make sure the totals still add up after every data load.

A report lists each household's nearest clinic within 1.5 km with a CROSS JOIN LATERAL query. It has fewer rows than there are households, and the team published it as complete. What happened, how do you fix the query, and what should the report say?

A "five nearest stores" query takes 300 ms on 2 million stores, although stores.geom has a GiST index. The SQL joins the customer's row to the stores and orders by distance. What do you look for in the plan, and how do you rewrite the query?

A fleet table takes 50 million GPS pings a day, appended in time order and never updated. Dashboards ask for the pings in one district during the last hour. Which indexes do you build, and why not a single GiST index on the geometry?

A radius endpoint answered in 3 ms for months. After a full nightly reload of its table it takes 40 ms, although the index exists and EXPLAIN still shows a Bitmap Index Scan. What do you check, and what fixes it for good?

A state's 30 district boundaries are simplified nightly with ST_SimplifyPreserveTopology for a dashboard. Every district is valid, yet analysts find slivers between neighbours and households counted in two districts. Explain why, and how you would produce simplified districts that still add up.

A city wants "hot spots" of dengue cases from 40,000 geocoded cases. DBSCAN with eps 50 m gives 2,000 tiny clusters; with eps 500 m, one cluster covers half the city. How do you choose eps and minpoints, and what do you report so that the result can be trusted?

A team generalises the national boundary layer inside PostGIS every night for web maps, and analysts now complain that areas and counts changed. Which processing belongs in the database, and how do you keep analytical and display geometry apart without two teams editing two copies?

A city map serves 5 million address points as vector tiles straight from PostGIS. Tiles at low zoom weigh over a megabyte and the map stutters while panning. What do you measure first, and what do you change in the tile SQL?

You must put a "clinics near me" endpoint backed by PostGIS on the public internet. What sits between the internet and the database, and which settings of the database and its roles do you change before launch?

A dashboard downloads 200,000 land parcels as one GeoJSON file of 90 MB before it draws anything. Users also want to click a parcel to see its details and to download a district's parcels. GeoJSON, vector tiles or OGC API - Features: which do you use for what?