Simple distance between two points

Should be really straight forward, but I can't understand what I'm doing wrong. I'm trying to measure the distance between the centre of a polygon (suburb) and an arbitrary point.

SELECT
ST_AsText(ST_Centroid(geom)) as poly_centroid
, ST_AsText(ST_SetSrid(ST_MakePoint(151.182995, -33.890395), 4283)) as pin
, ST_Distance(
ST_Transform(ST_SetSrid(ST_MakePoint(151.182995, -33.890395), 4283), 3112),
ST_Transform(ST_Centroid(geom), 3112)
) as dist
FROM public.suburbs

I'm using 3112, Australia Lambert projection (which is in metres) - and entering the arbitrary pin point in long/lat (EPSG 4283) - but the resulting distance is clearly wrong:

poly_centroid pin dist
POINT(150.9300421895883 -33.72537351810487) POINT(151.182995 -33.890395) 4229563.228668396
POINT(151.03083602374133 -33.75769279240827) POINT(151.182995 -33.890395) 4229563.161116713
POINT(151.15847466229258 -33.75004174996872) POINT(151.182995 -33.890395) 4229563.120652905
POINT(150.93371519943338 -33.73946132331832) POINT(151.182995 -33.890395) 4229563.214226549
POINT(150.97498052582384 -34.12744863603382) POINT(151.182995 -33.890395) 4229562.838806998

These should be 10s of Km apart, not 1000's.

Note that the polygons are all loaded in EPSG 3112 projection, so the ST_Transform isn't necessary.

• If SELECT ST_AsText(ST_Centroid(geom)) as poly_centroid returns POINT(150.9300421895883 -33.72537351810487) then geom obviously is not in EPSG:3112. Your SQL should still work but check what you get for the second term with select ST_AsText(ST_Transform(ST_Centroid(geom), 3112)). Commented Dec 23, 2020 at 8:09
• I believe that you have loaded some long-lat data into PostGIS and just assigned SRID EPSG:3112 without reprojecting the data. select st_distance( st_geomfromtext('POINT(150.9300421895883 -33.72537351810487)'), st_geomfromtext('POINT(1555654.93979939 -3903769.92078054)')) gives 4202231 meters, that is close to your numbers. Commented Dec 23, 2020 at 12:01

I get different values for the distances in my PostGIS. postgis_full_version string is:

POSTGIS="3.0.0 r17983" [EXTENSION] PGSQL="120" GEOS="3.8.0-CAPI-1.13.1 " PROJ="6.3.1" LIBXML="2.9.4" LIBJSON="0.13.1" LIBPROTOBUF="1.3.3" WAGYU="0.4.3 (Internal)"

I created a simple point table from your centroids:

CREATE TABLE centr(
id integer PRIMARY KEY,
geom geometry(Point, 4283)
);
INSERT INTO centr VALUES (1, ST_GeomFromText('POINT(150.9300421895883 -33.72537351810487)',4283));
INSERT INTO centr VALUES (2, ST_GeomFromText('POINT(151.03083602374133 -33.75769279240827)',4283));
INSERT INTO centr VALUES (3, ST_GeomFromText('POINT(151.15847466229258 -33.75004174996872)',4283));
INSERT INTO centr VALUES (4, ST_GeomFromText('POINT(150.93371519943338 -33.73946132331832)',4283));
INSERT INTO centr VALUES (5, ST_GeomFromText('POINT(150.97498052582384 -34.12744863603382)',4283));

and selected the distances:

SELECT id, ST_Distance(ST_Transform(ST_SetSrid(ST_MakePoint(151.182995, -33.890395), 4326), 3112),
ST_Transform(geom, 3112))
FROM centr;

and I got the following values:

id |    st_distance
----+--------------------
1 |  29565.26990848302
2 | 20264.268485336703
3 |   15648.1409013042
4 | 28358.850986011512
5 | 32405.833529177606

I checked the first one and looks correct. There is probably problem with your SRS definitions in spatial_ref_sys table. The definitions for EPSG 4283 and 3112 in my table are:

EPSG      |      4283 | +proj=longlat +ellps=GRS80 +towgs84=0,0,0,0,0,0,0 +no_defs
EPSG      |      3112 | +proj=lcc +lat_1=-18 +lat_2=-36 +lat_0=0 +lon_0=134 +x_0=0 +y_0=0 +ellps=GRS80 +towgs84=0,0,0,0,0,0,0 +units=m +no_defs
• You are correct I loaded the .shp file using the wrong SRID. Commented Dec 23, 2020 at 23:30

Looks like I missed an important step. The .shp file was encoded in EPSG 4283, but I loaded it in as EPSG 3112.

I had thought that the PostGIS Shapefile Import Manager transformed the shapefiles to the desired SRID, and auto identified the encoded SRID from the .prj file (which is how I use the command line tool). This is not how the import manager works, it requires the SRID to be manually entered - as far as I can tell, it doesn't appear to use the .prj file at all?