假设我有许多几何图形在我的数据库中。我想在PostgreSQL内创建一个函数,使得我可以传入经纬度,并返回给定半径内(按线性和道路距离)最接近的几何图形。
我是一个新手,所以任何建议都将不胜感激。
我正在运行以下版本:
PostgreSQL: 9.2
PostGIS: 2.0
以下是架构:
我是一个新手,所以任何建议都将不胜感激。
我正在运行以下版本:
PostgreSQL: 9.2
PostGIS: 2.0
以下是架构:
-- ----------------------------
-- Table structure for "cities-usa"
-- ----------------------------
DROP TABLE IF EXISTS "cities-usa";
CREATE TABLE "cities-usa" (
"gid" int4 NOT NULL DEFAULT nextval('"cities-usa_gid_seq"'::regclass),
"st_fips" varchar(4),
"sfips" varchar(2),
"county_fip" varchar(4),
"cfips" varchar(4),
"pl_fips" varchar(7),
"id" varchar(20),
"name" varchar(39),
"elevation" varchar(60),
"pop_1990" numeric,
"population" varchar(30),
"st" varchar(6),
"state" varchar(16),
"warngenlev" varchar(16),
"warngentyp" varchar(16),
"watch_warn" varchar(3),
"zwatch_war" float8,
"prog_disc" int4,
"zprog_disc" float8,
"comboflag" float8,
"land_water" varchar(16),
"recnum" float8,
"lon" float8,
"lat" float8,
"geom" "geometry"
)
WITH (OIDS=FALSE);
ALTER TABLE "cities-usa" OWNER TO "postgres";
-- ----------------------------
-- Primary key structure for table "cities-usa"
-- ----------------------------
ALTER TABLE "cities-usa" ADD CONSTRAINT "cities-usa_pkey" PRIMARY KEY ("gid") NOT DEFERRABLE INITIALLY IMMEDIATE;