3

I've a PostGIS database loaded with several tables filled with shapefiles. I'm using psycopg2 to access everything via python. I'm able to access the table names with the below query:

"""SELECT table_name FROM information_schema.tables WHERE table_schema = 'public'"""

which returns

('Sea Areas(PolyGrid)',) ('geography_columns',) ('geometry_columns',) ('spatial_ref_sys',) ('12NM(LineGrid)',) ('TimeZone(LineGrid)',) ('12NM_PolyGrid',)

But when i try and run a simple query like:

"""SELECT geom FROM 12NM_PolyGrid"""

it throws:

Error while connecting to PostgreSQL syntax error at or near "12"

I'm new to SQL so it could be simple error. Ultimately I would like to do a bounding box query like in this link. The results would then be placed in a geodataframe.

Improve this question
2
  • 1
    Best practice is to use legal SQL identifiers in table and column names. This means leading alpha, with alpha-numeric or underscore in the remaking characters, all lowercase. This permits case-insensitive access to columns. Using mixed-case identifiers is poor practice and using leading numerics is an antipattern. Since you are new at SQL, this is the best time to stop a practice that will cause failure and pain down the line. Do not use any identifier which would require double-quoting, and you won't have to use double-quotes. Commented Jan 5, 2020 at 13:12
  • 2
    Note that, while shapefiles might have been the source, your database does not have "several tables filled with shapefiles". Shapefile is an ancient file-based data format. The act of loading them is to the PG database creates PostGIS geometry columns (alongside other column types) in tables -- the data is no longer in shapefile format, so it would be more correct to describe as "several tables filled from shapefiles". Commented Jan 5, 2020 at 13:21

1 Answer 1

Reset to default
3

PostGreSQL (and thus PostGIS) don't like table (or column) names to start with digits or to have mixed case. So you need to add " around any name that does this. So, in your case the select statement is:

SELECT geom FROM "12NM_PolyGrid"

which in a python string should probably be:

'SELECT geom FROM "12NM_PolyGrid"'
Improve this answer
1
  • Perfect, cheers Ian, must have been one of the few combinations I hadn’t tried! Commented Jan 6, 2020 at 11:38

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.