postgis subqueries
I've built a query that pulls the data I need out my table using pgAdmin3's query builder, however, when I insert it into tilemill I get an error.
Query: (the table name is "city")
`SELECT city.geoname FROM
public.city WHERE
city.generiterm = 'Community (1)';
The error is:
Error: : ERROR: syntax error at or near "SELECT" LINE 1:
SELECT ST_SRID("the_geom") AS srid FROM SELECT ^ Full sql was:
'SELECT ST_SRID("the_geom") AS srid FROM SELECT city.geoname FROM
public.city WHERE city.generiterm = 'Community (1)'; WHERE
"the_geom" IS NOT NULL LIMIT 1;'
Comments are currently closed for this discussion. You can start a new one.
2 Posted by kris on 07 Sep, 2011 12:40 AM
my first code block didn't work.. this is the query:
SELECT city.geoname FROM public.city WHERE city.generiterm = 'Community (1)';3 Posted by atrawog on 07 Sep, 2011 01:12 AM
Tilemill automatically adds a "SELECT * FROM" and trailing ";" to your query.
So if you test your queries in pgadmin you should do in the form of: "SELECT * FROM (SELECT city.geoname FROM public.city WHERE city.generiterm = 'Community (1)');" and only copy the sub query including the parentheses to Tilemill and take extra care that you don't accidentally put any semicolon into your TileMill sub query.
Your combined " ... WHERE ... ; WHERE ... ; " query is definitely wrong.
4 Posted by kris on 07 Sep, 2011 01:14 AM
This ticket can be closed, springmeyer helped me out in IRC, I was missing brackets and as $vars
the following syntax did the trick:
(SELECT city.geoname, city.the_geom, city.generiterm FROM public.city WHERE city.generiterm = 'Community (1)') as citySupport Staff 5 Posted by Will White on 07 Sep, 2011 02:31 AM
Glad it's working now. Thanks!
Will White closed this discussion on 07 Sep, 2011 02:31 AM.