Press "Enter" to skip to content

Category: Data Types

GPS Data in PostGIS

Published 2023-12-19 by Kevin Feasel

Ryan Lambert clues us in:

One of the key elements to using PostGIS is having spatial data to work with! Lucky for us, one big difference today compared to the not-so-distant past is that essentially everyone is carrying a GPS unit with them nearly everywhere. This makes it easy to create your own GPS data that you can then load into PostGIS! This post explores some basics of loading GPS data to PostGIS and cleaning it for use. It turns out, GPS data fr om nearly any GPS-enabled device comes with some… character. Getting from the raw input to usable spatial data takes a bit of effort.

This post starts with using ogr2ogr to load the .gpx data to PostGIS. Once the data is in PostGIS then we actually want to do something with it. Before the data is completely usable, we should spend some time cleaning the data first. Technically you can start querying the data right away, however, I have found there is always data cleanup and processing involved first to make the data truly useful.

Click through for an example of how it all fits together.

Comments closed

A Look at UTF-8 in SQL Server

Published 2023-11-09 by Kevin Feasel

Daniel Hutmacher takes a peek:

A client asked me about SQL Server collations, and if they should consider the new UTF8 collations (new since SQL Server 2019). I tried to hide my blank stare of ignorance, and promised them I’d look it up and get back to them.

Not gonna lie, I think UTF and Unicode can be pretty confusing at times, so I did some googling and some testing, and here’s what I found.

Read on to see what Daniel found, and check the comments for one person’s unfortunate experience. For a deep dive into UTF-8 in SQL Server, I also recommend Solomon Rutzky’s blog post on the topic, noting that it was last updated in 2020 so some of the incompatibility section may not be relevant any longer.

Comments closed

Fast Key Optimization in SQL Server

Published 2023-10-20 by Kevin Feasel

Paul White explains the performance benefits of fast key optimization and when you can get them:

SQL Server can be called upon to sort a variety of data types. To facilitate this, the sorting code normally calls out to a specific comparator to determine how two compared values should sort: lower, higher, or equal.

Although calling comparator code has low overhead, performing enough of them can cause noticeable performance differences.

To address this, SQL Server has always (since at least version 7) supported a fast key optimization for simple data types. 

Click through to learn which data types support fast key optimization and to gain a feeling of the performance impact.

Comments closed

Oracle Errors: Snapshot Too Old and LOB Columns

Published 2023-10-12 by Kevin Feasel

David Fitzjarrell tackles a pair of errors:

One of the few errors taht strikes fear in the heart of a DBA is the dreaded:

ORA-01555 snapshot too old
and
ORA-22924 snapshot too old

Of course there are plenty of blogs instructing the DBA to simply “increase the undo_retention”, and there are cases where this works as expected. However, LOBs can be different as two different mechanisms exist for undo management. A LOB column can be configured to use retention to manage before mages of the data, but that can be confusing as each LOB column MAY have its own retention setting. The DBA_LOBS view reports whether LOG column uses retention or pctversion to manage undo, and the associated setting being used. Let’s -dig into this a bit deeper.

Read on to learn more about how LOB retention works, the types of issues you can run into with it, and how to correct those issues.

Comments closed

An Overview of Postgres Data Types

Published 2023-09-18 by Kevin Feasel

Arindam Mondal categorizes various Postgres data types:

This article will show PostgreSQL Data Types with various examples.

Data Types are an important part of a database. It represents values associated with it. Choosing the right data type for a table is one of the most important tasks because it determines the kind of data we want to store in a table. While creating a table you must specify a data type for each column. A column can store a specific type of data, like integer, string, Boolean, floating points, and so on. In this article, we are going to discuss PostgreSQL data types.

The list is quite similar to what’s available in SQL Server, though there are a few differences, such as built-in support for storing network addresses.

Comments closed

Lists and DataFrames in R

Published 2023-08-21 by Kevin Feasel

Adrian Tam continues a series on core data types in R:

Vectors in R are supposed to be of homogeneous data type. You can use a list as the container if there are mixed data types, such as numbers and strings. The list and data frame are closely related in R. The data frame is probably more useful because it reflects how we usually collect statistics. In this post, you will learn about them. Specifically, you will know:

  • What are lists and data frames in R
  • How to manipulate lists and data frames

Read on to learn more about these two sorts of collections.

Comments closed

Conversion from XML to Target Collation Impossible

Published 2023-07-20 by Kevin Feasel

Chad Callihan has a mission, should you choose to accept it:

I recently ran into an error for a query that was casting XML to VARCHAR to search for a value. I have a bit of familiarity with the XML for these types of queries, so I was surprised when running the SELECT statement caused this error, which I had never seen before:

Msg 6355 “Conversion of one or more characters from XML to target collation impossible”

Click through for the solution. The real solution? NVARCHAR Everywhere.

Comments closed