first of all , I have dataset like this in json
<code>{
"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"
}
},
...
]
}
</code>
<code>{
"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"
}
},
...
]
}
</code>
{
"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"
}
},
...
]
}
I dumped the data using this function:
<code>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;
// Ensure DISTRICT is a string
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);
}
};
</code>
<code>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;
// Ensure DISTRICT is a string
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);
}
};
</code>
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;
// Ensure DISTRICT is a string
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);
}
};
but i want to implement the getrequest based on lat and long
<code>
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" });
}
};
</code>
<code>
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" });
}
};
</code>
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" });
}
};
problem is : even i query the databased based on longitutue and lattitude from original json data, it returns empty list
i want the data which it corresponds to the given longitude and lattitude
New contributor
Samman Amgain is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.