1

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.

Vince
  • 20,017
  • 15
  • 45
  • 64
Tom Shelley
  • 133
  • 7
  • 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. – Vince Jan 05 '20 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". – Vince Jan 05 '20 at 13:21

1 Answers1

2

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"'
Ian Turton
  • 81,417
  • 6
  • 84
  • 185