DEV Community: Peter Perkins

Find/Extract Features by largest size (aggregate) 🌎

Peter Perkins — Mon, 14 Feb 2022 15:01:20 +0000

Say you have multiple shapes within parent shapes; in this case, buildings that belong on parcels of land. We can use the maximum() expression in QGIS to find the largest building on each parcel.

Prerequisites:

Keys: Each building shape needs a unique ID and a foreign key that associates it with the parcel (its parent). In this case I found the Pole Of Inaccessibility for each building and used that point in a Join by Location against the parcel to create a foreign key to create a relationship between the buildings and parcels.
Size: Each buildings needs a field that defines its area

The Expression:

*We use `Extract by Expression` tool with the parcels layer selected as the input layer.

"SHAPE_AREA" = maximum("SHAPE_AREA",group_by:="PARCEL_ID")

Notes:

Expression Breakdown: All that needs to be replaced is SHAPE_AREA and PARCEL_ID. Keep the remaining syntax the same. I was not familiar with the group_by:= syntax and thought this was a memo in documentation I read but this is literally part of the function. Image showing the function below.

Processing Time: In my tests I found that the processing time was not linear. What I mean by this is comparing 1000 features does not always take 10 times as long as 100 features. I believe the calculation is ~exponential meaning that the more features you add to compare, the longer the processing time by a factor of x (not sure exactly). If you have a large dataset that you are running this expression against (>50,000 features), I would recommend splitting up the layer you are processing into chunks, run the expression on each, and then merge. You should be able to split the layer without duplicates but if you want to be sure you can always run a Remove Duplicates expression after the merge at the end.

~56,000 features took about 110 minutes to process.

CSV record count not matching your source? 🙈

Peter Perkins — Mon, 31 Jan 2022 15:22:23 +0000

CSV record count not matching your source? You've probably got rouge carriage returns in the source data. If you're in MS Excel you can do a Search and Replace on the source data before saving as a CSV.

How To:

Search and Replace (in Excel). In the search box type Ctrl + J to insert a carriage return. It will look like there is nothing in the search box but its there. Click Find All to find and see all fields with carriage returns. To remove, simply click Replace All.

Import a CSV file into a MySQL database using PHP PDO (fast af) 🚀

Peter Perkins — Mon, 31 Jan 2022 00:20:14 +0000

Let say you're like me and need to get ~2Gib of records into a SQL database. Most of us are probably running a shared hosted server and have limitations regarding file upload size in phpMyAdmin. Honestly, after discovering this new method, I dont know that I'll ever upload big data another way.

Here is a great PHP Script from Stavros Skamagkis that uses PDO. It is pretty self explanatory. I've included a modified version below.

So how long does the import take? Well, in my case I have ~1,800,000 records with 30 columns and it takes seconds, like less than 5 😎.

Tips:

Define fieldseparator, fieldEscapedBy, fieldEnclosedBy, and lineSeperator as variables (shown in first few lines). I span my wheels a little defining them inline which did not work (I believe because of PDO).
CSV file location is relative to this files location.
Header? Use IGNORE 1 LINES to skip the first line.
How to run? I created a Development Button and linked onClick() that connects via Ajax to the resource. I'm know there are easier methods, I just already had it setup. Nevertheless, this works.

<?php
//  Code Source: https://gist.github.com/stavrossk/0f513ccbfe7882870ab1

$databasehost = "localhost";
$databasename = "YOUR_DATABASE_NAME";

$databasetable = "YOUR_DATABASE_TABLE";

$databaseusername = "YOUR_DATABASE_USERNAME";
$databasepassword = 'YOUR_DATABASE_PASSWORD';

$fieldSeparator = ";";
$fieldEscapedBy = "";
$fieldEnclosedBy = '"';
$lineSeparator = "\n";

$csvfile = "../FILE_TO_IMPORT.csv";


if (!file_exists($csvfile)) {
    error_log('File does NOT exist!');
    die("File not found. Make sure you specified the correct path.");
}

try {

    $pdo = new PDO(
        "mysql:host=$databasehost;dbname=$databasename",
        $databaseusername,
        $databasepassword,
        array(
            PDO::MYSQL_ATTR_LOCAL_INFILE => true,
            PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION
        )
    );
} catch (PDOException $e) {
    error_log('database connection failed!');
    die("database connection failed: " . $e->getMessage());
}

$affectedRows = $pdo->exec(
    "LOAD DATA LOCAL INFILE "
        . $pdo->quote($csvfile)
        . " INTO TABLE `$databasetable` FIELDS TERMINATED BY "
        . $pdo->quote($fieldSeparator)
        . " ESCAPED BY "
        . $pdo->quote($fieldEscapedBy)
        . " ENCLOSED BY "
        . $pdo->quote($fieldEnclosedBy)
        . " LINES TERMINATED BY "
        . $pdo->quote($lineSeparator)
        . " IGNORE 1 LINES "
);

echo "Loaded a total of $affectedRows records from this csv file.\n";
?>

DateTime->diff | Does It Round? (no)

Peter Perkins — Mon, 25 Oct 2021 13:10:24 +0000

In working with differences between timestamps I had a question that may seem trivial, however with the answer not clearly defined in the manual, I wanted to write up my findings 🤓

The question: Does the ->diff function that can be used with DateTime() round to the nearest day? For example:

error_log("DateTime round question");

$date_start = new DateTime('2021-10-22 00:00:00');
$date_end = new DateTime('2021-10-22 23:59:59');
$difference = $date_start->diff($date_end);

error_log("timeDifference: " . $difference->days);

The result to this is: timeDifference: 0;

The time differences are 1 second from being 1 day apart and the result of the difference is 0 days - perfect!

Learn Array Destructuring

Peter Perkins — Tue, 05 Oct 2021 13:35:36 +0000

👏 #mustknow

Learning JS or React? Learn to deconstruct arrays using the destructuring assignment syntax.

Example curtesy MND Web Docs

({ a, b } = { a: 10, b: 20 });
console.log(a); // 10
console.log(b); // 20

Text-Align Select (Safari Fix)

Peter Perkins — Sun, 03 Oct 2021 17:49:16 +0000

Have you ever tried to text-align a select element? Safari is not a fan (no support). The answer to this question has eluded me and seemingly the internet - so here we are with a solution.

Quick Answer

direction: rtl;
Code Pen Example

Let's start with a simple example. It is important to note that the width of a select element will be as wide as its longest option.

HTML

<select>
  <option>1</option>
  <option>10</option>
  <option>100</option>
  <option>1,000</option>
</select>

CSS

select {
    -webkit-appearance: none; /* turn off drop-down arrow*/
    -moz-appearance: none;
    border: solid 1px grey;
    font-size: 1rem;
    padding: 5px;
    /* direction: rtl; */
}

Styling 💅

The above styling for the input isn't necessary; presumably you would only pursue this article if you are making a custom input element though so why not pretty it up?

The Fix 🙌

Uncommenting the last line direction: rtl; will move the text inside the element to the left. In Chrome you could simply use text-align: left; however this will not work in Safari.

Bonus 🤌

Change Direction While Selecting Option

A simple line can be added to change the options alignment; essentially the text that will show when we are making a selection. To do this we can add:

.select:active {
    direction: ltr;
}