postgresql - Postgis funciton ST_Contains to write an sql query -
i having issues writing query uses postgis function st_contains. want display urban areas in state of colorado. actual definition of query here.
return names (name10) of urban areas (in alphabetical order) entirely contained within colorado. return results in alphabetical order. (64 records)
the tables using tl_2010_us_state10 (this stores information states). think going use name10 variable in table because has of names of states.
table "public.tl_2010_us_state10" column | type | modifiers ------------+-----------------------------+------------------------------------- gid | integer | not null default region10 | character varying(2) | division10 | character varying(2) | statefp10 | character varying(2) | statens10 | character varying(8) | geoid10 | character varying(2) | stusps10 | character varying(2) | name10 | character varying(100) | lsad10 | character varying(2) | mtfcc10 | character varying(5) | funcstat10 | character varying(1) | aland10 | double precision | awater10 | double precision | intptlat10 | character varying(11) | intptlon10 | character varying(12) | coords | geometry(multipolygon,4326) | indexes: "tl_2010_us_state10_pkey" primary key, btree (gid) "tl_2010_us_state10_coords_gist" gist (coords)
then have table displays urban information. once again think going use name10 variable because stores name of urban areas.
table "public.tl_2010_us_uac10" column | type | modifiers ------------+-----------------------------+------------------------------------- gid | integer | not null default uace10 | character varying(5) | geoid10 | character varying(5) | name10 | character varying(100) | namelsad10 | character varying(100) | lsad10 | character varying(2) | mtfcc10 | character varying(5) | uatyp10 | character varying(1) | funcstat10 | character varying(1) | aland10 | double precision | awater10 | double precision | intptlat10 | character varying(11) | intptlon10 | character varying(12) | coords | geometry(multipolygon,4326) | indexes: "tl_2010_us_uac10_pkey" primary key, btree (gid) "tl_2010_us_uac10_coords_gist" gist (coords)
the code wrote in sql
select a.name10 urban_area tl_2010_us_uac10 join tl_2010_us_state10 b on st_contains(b.gid = a.gid) b.name10 = 'colorado' order a.name10;
but error
error: function st_contains(integer, integer) not exist line 1: ...010_us_uac10 join tl_2010_us_state10 b on st_contain... ^ hint: no function matches given name , argument types. might need add explicit type casts.
st_contains requires 2 parameters, both of should of type geometry. therefore have made 2 mistakes.
- using int fields
- comparing them = rather supplying them separate parameters
by correcting these get
select a.name10 urban_area tl_2010_us_uac10 join tl_2010_us_state10 b on st_contains(b.coords, a.coords) b.name10 = 'colorado' order a.name10;
Comments
Post a Comment