How to convert SRID's on the fly and via command line, on INSERTION's query in PostGIS?

Question

We are working on a project that used shapefiles in order to build our first PostGIS database, through a tool called “PostGIS Shapefile Import/Export Manager”.

The problem is that now we need to create new tables onto that same PostGIS DB, through executing SQL commands line, as detailing bellow.

0 – VERSIONS:
a – PostGIS: 2.0.7 r13406
b – Geoserver: 2.12.1

1 – CREATING TABLE QUERY:

CREATE TABLE tabela (gid serial, nome varchar, geom geometry(Point, 32724));

2 – INSERTING A POINT QUERY:

INSERT INTO tabela (nome, geom)
VALUES('Teste Renan', ST_GeomFromText('POINT(-40.388527 -20.339450)', 32724));

By inserting that point via command line, Geoserver cannot correctly calculate “Lat/Long Bounding Box”, when I press the “Compute from native bounds” button, at the “New Layer” screen.
It give strange values, denoting some problem.

P.S.: I have properly set up EPSG 32724 (WGS84 24S).

By doing this process, when I try to preview that specific layer, nothing appear.

Another strange thing that I have noticed is different values for GEOM field, for the “almost” same place:
a - When created by “PostGIS Shapefile Import/Export Manager”:

0101000000DCD8FFFF83BD1541B1FE00804F915D41

or using st_asewkt command:

POINT(356192.999999417 7750974.00006072)

b – When created by command line:

0101000020D47F0000774EB340BB3144C090A0F831E65634C0

or using st_asewkt command:

SRID=32724;POINT(-40.388527 -20.33945)

Any thoughts on how to solve this problem or how to properly create the query for add point to TABELA table?

Answer 1

You will need to project those points to your CRS; when using ST_GeomFromText(<point>, <srid>), the given coordinates are treated as units in respect to the given SRID (so, in your case, that sets your point roughly at the equator and next to the UTM zone's reference meridian, as the values are treated as meter).

Try

INSERT INTO tabela (nome, geom)
VALUES('Teste Renan', ST_Transform(ST_GeomFromText('POINT(-40.388527 -20.339450)', 4326), 32724));

Note: this assumes your initial geographic coordinates are based on EPSG:4326.