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.

  1. using int fields
  2. 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

Popular posts from this blog

c++ - QTextObjectInterface with Qml TextEdit (QQuickTextEdit) -

javascript - angular ng-required radio button not toggling required off in firefox 33, OK in chrome -

xcode - Swift Playground - Files are not readable -