一、相关空间表简介及设计
本节重点介绍需要使用到的一些基础空间表和用于存储省级城市距离的空间表信息。通过本节的讲解,了解如何进行省级地市的信息查询,以及如何存储省级城市距离及其路线信息。
1、相关基础空间表
为了查询省会城市和其他地级城市,这里需要用到两张表:biz_geographic_name(PostGIS 空间表)和 biz_city(城市信息表)。通过省级行政区划代码找到 biz_city 表的城市信息,然后查找对应的包含的省会及地区城市信息。
CREATE TABLE "public"."biz_city" (
"id" int8 NOT NULL,
"province_code" varchar(16) COLLATE "pg_catalog"."default" NOT NULL,
"province_name" varchar(64) COLLATE "pg_catalog"."default" NOT NULL,
"city_code" varchar(16) COLLATE "pg_catalog"."default" NOT NULL,
"city_name" varchar(512) COLLATE "pg_catalog"."default" NOT NULL,
"type" varchar(32) COLLATE "pg_catalog"."default",
"geom" "public"."geometry",
CONSTRAINT "pk_biz_city" PRIMARY KEY ("id")
);
CREATE INDEX "idx_biz_city_citycode" ON "public"."biz_city" USING btree ("city_code" COLLATE "pg_catalog"."text_ops" ASC NULLS LAST);
CREATE INDEX "idx_biz_city_pcode" ON "public"."biz_city" USING btree ("province_code" COLLATE "pg_catalog"."text_ops" ASC NULLS LAST);
COMMENT ON COLUMN "public"."biz_city"."id" ;
COMMENT "public"."biz_city"."province_code" ;
COMMENT "public"."biz_city"."province_name" ;
COMMENT "public"."biz_city"."city_code" ;
COMMENT "public"."biz_city"."city_name" ;
COMMENT "public"."biz_city"."type" ;
COMMENT "public"."biz_city"."geom" ;
"public"."biz_geographic_name" (
"pk_id" int8 ,
"name" () "pg_catalog"."default" ,
"pinyin" () "pg_catalog"."default",
"classz" () "pg_catalog"."default",
"bz" () "pg_catalog"."default",
"slx" () "pg_catalog"."default",
"geom" "public"."geometry" ,
"pk_biz_geographic_name" ("pk_id")
);
INDEX "idex_biz_geographic_name_classz" "public"."biz_geographic_name" btree ("classz" "pg_catalog"."text_ops" );
INDEX "idx_biz_geographic_name_geom" "public"."biz_geographic_name" gist ("geom" "public"."gist_geometry_ops_2d");
COMMENT "public"."biz_geographic_name"."pk_id" ;
COMMENT "public"."biz_geographic_name"."name" ;
COMMENT "public"."biz_geographic_name"."pinyin" ;
COMMENT "public"."biz_geographic_name"."classz" ;
COMMENT "public"."biz_geographic_name"."bz" ;
COMMENT "public"."biz_geographic_name"."slx" ;
COMMENT "public"."biz_geographic_name"."geom" ;
COMMENT "public"."biz_geographic_name" ;









