Simple distance between two points

Question

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.

Answer 1

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 

Answer 2

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?