Here's my code:
<?php
$db = new PDO('mysql:dbname=dbname;host=host', 'user', 'pass');
$ne = explode(',', $_GET['ne']);
$sw = explode(',', $_GET['sw']);
$shape = "Polygon((
$sw[0] $sw[1],
$ne[0] $sw[1],
$ne[0] $ne[1],
$sw[0] $ne[1],
$sw[0] $sw[1]
))";
$sql_where = '';
if (strlen($_GET['exclude'])) {
$exclude = array_map('intval', explode(',', $_GET['exclude']));
if (count($exclude)) {
$exclude = implode(',', $exclude);
$sql_where = "AND countyfp NOT IN ($exclude)";
}
}
$q = $db->prepare("
SELECT
name,
countyfp AS fips,
ST_AsGeoJson(SHAPE) AS shape
FROM county_shapes
WHERE MBRIntersects(ST_GeomFromText(:shape, 4269), SHAPE)
$sql_where
");
$q->execute(compact('shape'));
$result = [];
while ($row = $q->fetch(PDO::FETCH_ASSOC)) {
$row['shape'] = json_decode($row['shape']);
$result[] = $row;
}
echo json_encode($result);
Can it be improved upon? I don't like the idea of dumping a variable into an SQL query but I'm not sure what other options I have.
Here's the schema:
CREATE TABLE `county_shapes` (
`OGR_FID` int NOT NULL AUTO_INCREMENT,
`SHAPE` geometry NOT NULL /*!80003 SRID 4269 */,
`statefp` varchar(2) DEFAULT NULL,
`countyfp` varchar(3) DEFAULT NULL,
`countyns` varchar(8) DEFAULT NULL,
`geoid` varchar(5) DEFAULT NULL,
`name` varchar(100) DEFAULT NULL,
`namelsad` varchar(100) DEFAULT NULL,
`lsad` varchar(2) DEFAULT NULL,
`classfp` varchar(2) DEFAULT NULL,
`mtfcc` varchar(5) DEFAULT NULL,
`csafp` varchar(3) DEFAULT NULL,
`cbsafp` varchar(5) DEFAULT NULL,
`metdivfp` varchar(5) DEFAULT NULL,
`funcstat` varchar(1) DEFAULT NULL,
`aland` decimal(14,0) DEFAULT NULL,
`awater` decimal(14,0) DEFAULT NULL,
`intptlat` varchar(11) DEFAULT NULL,
`intptlon` varchar(12) DEFAULT NULL,
PRIMARY KEY `OGR_FID` (`OGR_FID`),
SPATIAL KEY `SHAPE` (`SHAPE`)
) ENGINE=InnoDB;
The server is MySQL 8.0.28.