使用Postgis函数编写SQL查询时输出错误


Wrong output when writing a SQL query using Postgis functions

我正在做作业,我即将完成它。问题是我正确的最后一个查询应该只返回10行,但我现在的查询返回16行,我不知道我做错了什么。查询的定义是

Find all urban areas that (1) have a combined land & water area of greater than 1500 square 
kilo- meters and (2) intersect multiple states. Your query should return the urban area name 
(name10) and a count of the number of states intersected. The results should be first ordered by 
the number of states intersected (in descending order) and secondarily by alphabetical order of 
the urban area names from (A to Z). (Note: Pay very close attention to the units used for the 
areas in this question.) (10 records) 

我编写的返回16条记录的SQL查询是

SELECT area1.name10, COUNT(*) AS Count 
FROM tl_2010_us_uac10 AS area1, tl_2010_us_state10 AS area2 
WHERE ((area1.aland10 + area1.awater10)/1000) > 1500 
AND ST_Intersects(area1.coords, area2.coords) 
GROUP BY area1.name10 HAVING COUNT(*) > 2 
ORDER BY Count DESC, area1.name10 ASC;

我使用的两张表是

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)

这是城市信息表。

                                      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)

提前感谢

面积以平方米为单位。要将平方米转换为平方公里,请除以1000000,而不是1000。

1公里=1000米。1km2=(1000m)**2=1000000(m2)