2

I'm using the UK crime dataset provided by https://data.police.uk/

My question is about coordinate systems/map projections. The crime locations are stored with the projection EPSG 4326 which uses degrees as units. But to do some distance calculations, I would like to use meters.

From what I could research there are many projections and depending on the use-case, some are better than others. In this case, I encountered the British National Grid(EPSG: 27700) which gives accurate distances as long as it’s within the United Kingdom. In PostGIS, I can transform geometries that are EPSG 4326 to 27700 using ST_Transform(https://postgis.net/docs/ST_Transform.html ) and calculate that way. However if I want to display in a Web Map, which usually uses EPSG:3857 to display the map, I need to convert the output of the query to EPSG:3857?

In a nutshell, convert from 4326 to 27700 to do calculations, then convert the output to 3857 to display on the map. Do you think this procedure makes sense? If not, can you explain what I should know/do to tackle this problem?

Based on this assumption I would do something like this:

-- get all streets within a radius of 100 meters
-- where the center is a crime point with ID=1

SELECT street_id, ST_Transform(street_geom, 3857)
FROM streets
INNER JOIN crimes_street as cst
    ON ST_DWithin(ST_Transform(street_geom, 27700), ST_Transform(cst.longlat_point, 27700), 100)
WHERE cst.crime_street_id = 1
6
  • why not use 27700 through out?
    – Ian Turton
    Commented Apr 30, 2022 at 8:27
  • 3
    Stick with 4326, cast to GEOGRAPHY to calculate distances and, if needed, transform to 3857 - some web map client libraries expect data in 4326, too, and transform internally.
    – geozelot
    Commented Apr 30, 2022 at 9:24
  • @IanTurton I'm new to GIS, so what I say might not make sense. I thought that storing in 4326 would be beneficial because it's a "universal projection". Not restricted by any location unlike 27700. If I store them in 27700 they would only be usable with a map that uses 27700 projection. Commented Apr 30, 2022 at 13:32
  • 2
    Use the 27700 to calculate your distances to a field in your dataset and make a note of that on any map. You can reproject your data after that to any other coordinate system, also making a note of that. But if you are using QGIS or ArcGIS they will handle the transformation between coordinate systems on the fly (automatically) so you don't need to make different coordinate system versions.
    – John
    Commented Apr 30, 2022 at 14:23
  • 1
    Most functions that accept geometry can also accept a geography object case to geometry. E.g. obj::geometry.
    – dr_jts
    Commented Feb 16, 2023 at 17:02

1 Answer 1

0

From what I can gather of your question, you are mixing the display of the map with the output of the calculations, which will be a table.

...the output of the query...

is tabular??

Use the ST_Transform to do the calculation, as you would like.

1
  • It's tabular. I added a query to the question to show my assumptions. I believe it allows to understand the problem better. Commented Apr 30, 2022 at 14:05

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.