1

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”.

Tool Screeen

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).

Usual and weird values.

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?

  • 2
    Please edit your question title to focus on one question, as per the Tour – Vince Sep 3 '18 at 20:58
  • 2
    Bounding box of a single point is just a point. That is correct but may give troubles for GeoServer. Increase the bounds to a real box manually. Also, your point seems to be in EPSG:4326. – user30184 Sep 3 '18 at 21:25
  • Sorry @Vince, hope the title is OK now. Thanks for the comment, @user30184! – Renan Menelli Sep 4 '18 at 12:55
3

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.

| improve this answer | |
  • I may am commiting a mistake, due to forum's policy, but I want to THANK YOU, @ThingumaBob! Your query worked out PERFECTLY on our project's environment. – Renan Menelli Sep 4 '18 at 12:52
  • 1
    @RenanMenelli well, you're welcome! ...apart from GeoServers quirks, e.g. the comment by user30184, I cannot stress out enough how essential it is to know your projections and how PostGIS works with them. this is a massive topic, but fundamental. you will be terrorized by errors and missing geometries and whatnot in your workflow if you make mistakes here...get a good read, is what I'm trying to say ,) – geozelot Sep 4 '18 at 13:55

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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