1
\$\begingroup\$

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.

\$\endgroup\$
4
  • 1
    \$\begingroup\$ Welcome to Code Review! Please read the sql tag wiki - especially the Question Guidelines section. Before reviewing this code, it would be helpful to have some more information about the data. \$\endgroup\$ Commented Sep 29, 2022 at 6:10
  • 1
    \$\begingroup\$ stackoverflow.com/a/14767651/285587 not that it will make your code much cleaner but at least there will be no outside variables in SQL \$\endgroup\$ Commented Sep 29, 2022 at 8:21
  • 1
    \$\begingroup\$ Welcome to Code Review! To help reviewers give you better answers, we need to know what the code is intended to achieve. Please add sufficient context to your question to describe the purpose of the code. We want to know why much more than how. The more you tell us about what your code is for, the easier it will be for reviewers to help you. Also, edit the title to simply summarise the task, rather than your concerns about the code. \$\endgroup\$ Commented Sep 29, 2022 at 10:21
  • 1
    \$\begingroup\$ @neubert don't bother. They always write such comments when they don't know what to answer. \$\endgroup\$ Commented Sep 30, 2022 at 8:25

0

Browse other questions tagged or ask your own question.