PostGIS PostgreSQL：经纬度查询行政区数据返回空结果求助

一、背景信息 #

1. 行政区GeoJSON数据集 #

{
  "type": "FeatureCollection",
  "features": [
    {
      "type": "Feature",
      "geometry": {
        "type": "Polygon",
        "coordinates": [
          [
            [87.62457877000008, 27.362144082000043],
            ...
          ]
        ]
      },
      "properties": {
        "STATE_CODE": 1,
        "DISTRICT": "TAPLEJUNG",
        "GaPa_NaPa": "Aathrai Tribeni",
        "Type_GN": "Gaunpalika",
        "Province": "1"
      }
    },
    ...
  ]
}

2. 数据导入代码（Knex.js + PostGIS） #

const knex = require("./connection");
const fs = require("fs");

const insertData = async () => {
  console.log("Inserting municipality data");
  const jsonData = JSON.parse(
    fs.readFileSync("utils/municipality.json", "utf8")
  );

  const dataToInsert = jsonData.features.map((data) => {
    const { properties, geometry } = data;
    let {
      STATE_CODE,
      DISTRICT = "",
      GaPa_NaPa,
      Type_GN,
      Province,
    } = properties;

    // 确保字段为字符串类型
    DISTRICT = DISTRICT.toString();
    Province = Province.toString();
    GaPa_NaPa = GaPa_NaPa.toString();

    return {
      state_code: STATE_CODE,
      district: DISTRICT,
      gapa_napa: GaPa_NaPa,
      type_gn: Type_GN,
      province: Province,
      geometry: knex.raw(
        `ST_SetSRID(ST_GeomFromGeoJSON('${JSON.stringify(geometry)}'), 4326)`
      ),
    };
  });

  try {
    const tableExists = await knex.schema.hasTable("tbl_municipality");
    if (tableExists) {
      await knex("tbl_municipality").insert(dataToInsert);
      console.log("Municipality data inserted");
    } else {
      console.log("tbl_municipality table does not exist");
    }
  } catch (err) {
    console.error("Error inserting municipality data", err);
  }
};

3. 查询接口代码（根据经纬度查行政区） #

const getMunicipalityData = async function (req, res, next) {
  let { lat, long } = req.query;
  if (lat === undefined || long === undefined) {
    lat = 27;
    long = 87;
  }

  console.log(lat, long);
  const rawQuery = `
     SELECT *
    FROM tbl_municipality
    WHERE ST_Intersects(
      geometry,
      ST_Buffer(ST_SetSRID(ST_MakePoint(?, ?), 4326), 01)
    )
  ;

  try {
    const result = await knex.raw(rawQuery, [long, lat]);
    const data = result.rows;
    console.log("Query result:", data);
    res.status(200).json({ success: true, data });
  } catch (error) {
    console.error("Error querying the database:", error);
    res.status(500).json({ success: false, message: "Database query failed" });
  }
};

问题 #

传入原始GeoJSON数据中存在的经纬度进行查询时，接口始终返回空列表，需修复该问题以获取对应行政区数据。

二、问题排查与修复方案 #

1. 修正查询语句的缓冲参数与逻辑 #

原查询中ST_Buffer(..., 01)存在两个问题：

• 01会被PostgreSQL解析为八进制数1，若无需缓冲应改为0，或直接去掉缓冲用点判断
• 用ST_Contains更适合判断点是否在多边形内部，比ST_Intersects+缓冲更准确

修改后的查询语句：

SELECT *
FROM tbl_municipality
WHERE ST_Contains(
  geometry,
  ST_SetSRID(ST_MakePoint(?, ?), 4326)
)

2. 修复导入代码的SQL注入与转义问题 #

原导入代码直接将JSON.stringify(geometry)拼入SQL，若几何数据含特殊字符会导致转义错误，改用参数绑定：

geometry: knex.raw(
  'ST_SetSRID(ST_GeomFromGeoJSON(?), 4326)',
  [JSON.stringify(geometry)]
),

3. 校验数据库表结构 #

确保tbl_municipality的geometry字段类型为GEOMETRY(POLYGON, 4326)，可执行以下SQL验证：

SELECT column_name, data_type, udt_name 
FROM information_schema.columns 
WHERE table_name = 'tbl_municipality';

4. 完善接口参数校验 #

增加参数类型校验，确保传入的经纬度是有效数字：

if (lat === undefined || long === undefined || isNaN(lat) || isNaN(long)) {
  lat = 27;
  long = 87;
}
// 转换为浮点型
lat = parseFloat(lat);
long = parseFloat(long);

三、修复后的完整代码 #

修复后的查询接口代码 #

const getMunicipalityData = async function (req, res, next) {
  let { lat, long } = req.query;
  // 校验参数有效性
  if (lat === undefined || long === undefined || isNaN(lat) || isNaN(long)) {
    lat = 27;
    long = 87;
  }
  // 转换为数字类型
  lat = parseFloat(lat);
  long = parseFloat(long);

  console.log(lat, long);
  const rawQuery = `
    SELECT *
    FROM tbl_municipality
    WHERE ST_Contains(
      geometry,
      ST_SetSRID(ST_MakePoint(?, ?), 4326)
    )
  `;

  try {
    const result = await knex.raw(rawQuery, [long, lat]);
    const data = result.rows;
    console.log("Query result:", data);
    res.status(200).json({ success: true, data });
  } catch (error) {
    console.error("Error querying the database:", error);
    res.status(500).json({ success: false, message: "Database query failed" });
  }
};

修复后的导入代码 #

const knex = require("./connection");
const fs = require("fs");

const insertData = async () => {
  console.log("Inserting municipality data");
  const jsonData = JSON.parse(
    fs.readFileSync("utils/municipality.json", "utf8")
  );

  const dataToInsert = jsonData.features.map((data) => {
    const { properties, geometry } = data;
    let {
      STATE_CODE,
      DISTRICT = "",
      GaPa_NaPa,
      Type_GN,
      Province,
    } = properties;

    // 确保字段为字符串类型
    DISTRICT = DISTRICT.toString();
    Province = Province.toString();
    GaPa_NaPa = GaPa_NaPa.toString();

    return {
      state_code: STATE_CODE,
      district: DISTRICT,
      gapa_napa: GaPa_NaPa,
      type_gn: Type_GN,
      province: Province,
      // 使用参数绑定避免转义错误
      geometry: knex.raw(
        'ST_SetSRID(ST_GeomFromGeoJSON(?), 4326)',
        [JSON.stringify(geometry)]
      ),
    };
  });

  try {
    const tableExists = await knex.schema.hasTable("tbl_municipality");
    if (tableExists) {
      await knex("tbl_municipality").insert(dataToInsert);
      console.log("Municipality data inserted");
    } else {
      console.log("tbl_municipality table does not exist");
    }
  } catch (err) {
    console.error("Error inserting municipality data", err);
  }
};

内容的提问来源于stack exchange，提问作者Samman Amgain