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.

  • 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 5 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 5 at 13:21
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"' 
|improve this answer|||||
  • Perfect, cheers Ian, must have been one of the few combinations I hadn’t tried! – Tom Shelley Jan 6 at 11:38

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.