Technology Application Archives - Page 2 of 2

Coordinate Reference Systems (CRS) and Geodetic Datums: What’s the difference?

Oct 12, 2023 | Data Domain, Technology Application

Coordinate Reference Systems (CRS) and geodetic datums are both used to represent the Earth’s surface, but they are different concepts, and importantly, serve different purposes. We provide definitions, highlight their differences and considerations for practical applications.

Coordinate Reference System (CRS)

A CRS is a coordinate-based system that provides a standardised framework for describing and locating points on the Earth’s surface. CRS is primarily used to represent specific locations on the Earth’s surface with precision and consistency.

A CRS can also be referred to as a spatial reference system (SRS) in many cases.

It defines a set of coordinates that can be used to represent the location of a point on the Earth’s surface.

A CRS typically includes a reference point (an origin), a set of axes (coordinate axes), and a unit of measurement (such as metres).

Geodetic Datum

A geodetic datum, on the other hand, is a mathematical model that defines the shape and size of the Earth’s surface, as well as the location of a reference point (the geodetic origin) and the orientation of the axes.

A geodetic datum provides the framework for measuring and comparing positions on the Earth’s surface.

It includes parameters describing the Earth’s ellipsoidal shape (semi-major and semi-minor axes), the flattening of the Earth, and the position of the datum origin.

Geodetic datums are essential for achieving high accuracy in geospatial measurements, especially over large areas.

What’s the difference?

While a CRS and a geodetic datum both provide frameworks for representing the Earth’s surface, they are different in their scope and purpose.

They serve distinct purposes in spatial representation and measurement.

The main differences between Coordinate Reference Systems and Geodetic Datums

	Coordinate Reference Systems (CRS)	Geodetic Datums
USES	A CRS is used to represent the location of a point on the Earth's surface	A geodetic datum is used to define the shape and size of the Earth's surface and the reference point used to measure positions
PRIMARY FOCUS	A CRS deals primarily with coordinate system	A geodetic datum deals with the underlying shape and size of the Earth's reference ellipsoid
DEFINITIONS	CRS definitions typically remain consistent	Geodetic datums may evolve over time due to improvements in measurement techniques and advancements in geodesy
OPTIONS	Multiple CRS are available	Multiple geodetic datums are available

It’s important to note that in many cases, CRSs are defined based on specific geodetic datums, ensuring compatibility and accuracy in spatial representations.

For example, the UTM system uses the WGS84 geodetic datum.

The decision between which CRS or geodetic datum to use

There are multiple choices of both CRS and geodetic datums available for users to select from.

The choice of CRS and geodetic datum depends on various factors such as the geographic region, application, and desired level of accuracy.

Geographic Region

Different regions of the world may use specific CRS and geodetic datum combinations that are optimised for that region’s geographical characteristics.

Learn about the geodetic datums we use and reference in Australia.

Application

The type of application you’re working on can influence your choice of CRS and geodetic datum.

For example, surveying and mapping applications often require high accuracy, so a CRS and geodetic datum that offer precision are chosen. On the other hand, less accurate CRS and datum choices may be suitable for applications like general-purpose Geographic Information Systems or web mapping.

Desired Level of Accuracy

The level of precision required for a particular project or task is a crucial deciding factor. Some CRS and geodetic datum combinations are designed to provide centimetre-level accuracy, while others may provide accuracy at the metre or even decimetre level. So the choice really depends on the project’s specific accuracy requirements.

In practice, these above factors need to be carefully considered to ensure users choose the CRS and geodetic datum that is appropriate and aligns to their needs.

Considerations include whether it accurately represents geospatial data, can be integrated seamlessly with other data sources or used in specific analysis or modeling purposes. This will help avoid errors and inconsistencies in geospatial data handling and analysis.

Practical uses for CRS and geodetic datums

In practical terms, when working with geospatial data and mapping, you often need to specify both the CRS and the geodetic datum to ensure accurate and consistent spatial referencing and calculations. Keep in mind different geographic regions and applications may use specific datums and CRS to meet their needs, so understanding the distinction between them is essential for accurate geospatial referencing and analysis.

How to set these in Snowflake

If using a Geography data type the CRS used is WGS 84 and cannot be changed.

If using the Geometry data type, the CRS (or SRS) can be set with the ST_SETSRID function. To change the CRS of a geometry, use the ST_TRANSFORM function.

SELECT ST_TRANSFORM( ST_GEOMFROMWKT('POINT(389866.35 5819003.03)', 32633), 3857 ) AS transformed_geom;

Useful Websites for Australian Properties, Parcels and Addresses

Researching the existence of a property or verifying data can be challenging, often resulting in conflicting answers. Depending on your definition of a property, it may be necessary to visit various websites to validate property data.

Understanding Housing Affordability: Key Metrics and Statistics

Housing affordability is a significant concern in many parts of the world, affecting the quality of life and economic wellbeing of individuals and families.

To understand the dynamics of housing affordability we need to take a detailed look at a range of different metrics and statistics to gain a full picture.

The Three Primary Methods of Real Estate Data Integration

cLearn the three primary methods of real estate data integration—geospatial relationships, title matches, and address matching—to improve accuracy, insights, and decision-making.

What’s the Difference Between GDA94 and GDA2020?

Geodetic datums, or geodetic systems, are often used by proptechs. Here is a rundown of everything you need to know about the different geodetic datums we use and reference in Australia.

Alternative Data: What Is It, Who Uses It And Why It Matters

Discover the powerful intel alternative data can offer. Learn why businesses and investors are turning to non-traditional data sources for deeper insights and smarter decisions.

Geohash vs H3: Which Geospatial Indexing System Should I Use?

Sep 29, 2023 | Technology Application

For years, the go-to geospatial indexing system has been Geohash. However, a relative new contender has emerged, challenging the status quo – H3. So should you use Geohash or H3?

Here, we’ll explore the differences between Geohash and H3, to help you decide which geospatial indexing system best suits your needs.

Geohash: A Familiar Friend

Geohash is a widely-used geocoding system that encodes geographic coordinates into a short string of letters and numbers. It divides the world into a grid of rectangles, each with a unique Geohash code. The longer the Geohash string, the more precise the location it represents.

H3: The Challenger

H3, on the other hand, is a relatively newer geospatial indexing system that’s gaining traction for its unique approach. Developed by ride-sharing company Uber, H3 uses a hexagonal grid to represent the Earth’s surface. Each hexagon is assigned a unique H3 index, offering a different perspective on geospatial indexing compared to Geohash.

Comparing Geohash and H3

We delve into the main differences between Geohash and H3 on a number of measures.

Precision

Geohash: Precision varies based on the length of the code. Longer codes are more precise, but this increases storage and complexity.
H3: H3 offers consistent precision regardless of location. Hexagons can be further subdivided for more precision, ensuring uniformity.

Spatial Relationships

Geohash: Geohash’s rectangular grid can struggle to represent spatial relationships accurately, especially near the poles (it should be noted that realistically, this is not going to be an issue in most use cases).
H3: H3’s hexagonal grid provides better spatial relationships, making it ideal for applications like ride-sharing services and navigation.

Support and Ease of Use

Geohash: Geohash is simple and widely adopted, making it easier to find resources and libraries for various programming languages.
H3: While H3 is gaining popularity, it may not have the same level of community support and resources as Geohash.

Applications

Geohash: Geohash is well-suited for applications that require basic geospatial indexing, such as location-based search or geofencing.
H3: H3 shines in complex applications like urban planning, logistics, and ride-sharing due to its consistent precision and better spatial relationships.

Scalability

Geohash: As Geohash codes get longer for more precision, storage and indexing can become inefficient.
H3: H3 scales more efficiently because it maintains uniform precision, regardless of location.

Source: H3

Geohash or H3: Choosing the right system

When it comes down to the choice between Geohash and H3, it really depends on your specific needs:

If you require a straightforward geospatial indexing system for basic applications, Geohash is a reliable choice with extensive community support.
On the other hand, if you’re dealing with complex spatial relationships, require consistent precision, or are working on innovative projects like urban planning or ride-sharing services, H3 offers a more promising solution. In the real estate context, it can be useful in urban planning, geofencing, spatial analysis, property market analysis.

Geospatial indexing is a fundamental technique used to manage and organise geographic or location-based data efficiently, in order to make data-based decisions or enhance applications.

Geohash is the old guard, tried and tested, while H3 is the newcomer with fresh ideas and uniform precision.

As we can see, both Geohash and H3 have their merits. However, the ultimate decision of which system to use should be based on the requirements of your project.

Snowflake releases H3 functionality

Snowflake provides SQL functions that enable you to use H3 with GEOGRAPHY objects.
This preview feature is now available to all accounts.

Useful Websites for Australian Properties, Parcels and Addresses

Understanding Housing Affordability: Key Metrics and Statistics

Housing affordability is a significant concern in many parts of the world, affecting the quality of life and economic wellbeing of individuals and families.

To understand the dynamics of housing affordability we need to take a detailed look at a range of different metrics and statistics to gain a full picture.

The Three Primary Methods of Real Estate Data Integration

cLearn the three primary methods of real estate data integration—geospatial relationships, title matches, and address matching—to improve accuracy, insights, and decision-making.

What’s the Difference Between GDA94 and GDA2020?

Geodetic datums, or geodetic systems, are often used by proptechs. Here is a rundown of everything you need to know about the different geodetic datums we use and reference in Australia.

Alternative Data: What Is It, Who Uses It And Why It Matters

Discover the powerful intel alternative data can offer. Learn why businesses and investors are turning to non-traditional data sources for deeper insights and smarter decisions.

Geohashes and Efficient Geospatial Joins in Snowflake

Sep 28, 2023 | Technology Application

Geohashes are an incredibly useful tool when it comes to spatial analysis. They serve as an encoding system that translates geographic coordinates into a short string of letters and digits, which simplifies and optimises geospatial operations.

One area where geohashes shine is in making geospatial joins more efficient. In this blog, we’ll dive into what geohashes are, and how you can leverage Snowflake’s ST_GEOHASH function to improve your geospatial joins in Snowflake.

What is a geohash?

A geohash is a hierarchical spatial data structure that subdivides space into a grid of cells, each cell having a unique string identifier. Geohashes convert a two-dimensional geographic coordinate (latitude and longitude) into this alphanumeric string. The length of the string determines the precision of the geohash; a longer string means a more precise location.

Read our blog on What is a Geohash for a detailed overview.

Source: Geohash Explorer

How geohashes make geospatial joins more efficient

Geospatial joins can be computationally expensive because they often require pairing each record in one dataset with every record in another to calculate distances or find overlaps. This can lead to a computational complexity of O(N*M), which is not ideal for large datasets.

Geohashes simplify this problem by converting the geospatial coordinates into strings. When you want to join based on geographic proximity, you can simply perform a string comparison, which is far less computationally expensive than a full spatial join.

Snowflake and ST_GEOHASH

Snowflake offers native support for geospatial functions, including ST_GEOHASH. Below is a simple example of how you can use this function to create a geohash in Snowflake:

-- Create a geohash for a specific latitude and longitude
SELECT ST_GEOHASH(37.7749, -122.4194, 12) AS geohash;

In this example, 37.7749 is the latitude, -122.4194 is the longitude, and 12 is the precision of the geohash.

To perform a geospatial join using geohashes, you can do the following:

-- Create two tables with geospatial data
CREATE TABLE locations1 (id INT, latitude FLOAT, longitude FLOAT);
CREATE TABLE locations2 (id INT, latitude FLOAT, longitude FLOAT);

-- Populate tables (this is just a representation)
-- ...

-- Add a geohash column to both tables
ALTER TABLE locations1 ADD COLUMN geohash STRING;
ALTER TABLE locations2 ADD COLUMN geohash STRING;

-- Update the geohash columns using ST_GEOHASH
UPDATE locations1 SET geohash = ST_GEOHASH(latitude, longitude, 12);
UPDATE locations2 SET geohash = ST_GEOHASH(latitude, longitude, 12);

-- Perform the join using the geohash
SELECT a.*, b.*
FROM locations1 a, locations2 b
WHERE a.geohash = b.geohash;

Geohash – Streamlining geospatial joins

Geohashes offer a streamlined way to perform geospatial joins, drastically reducing the computational resources required. With native functions like ST_GEOHASH in Snowflake, it’s easier than ever to incorporate geohashes into your geospatial workflows. By leveraging the power of geohashes, you can perform complex geospatial analyses more efficiently, saving both time and money.

Useful Websites for Australian Properties, Parcels and Addresses

Understanding Housing Affordability: Key Metrics and Statistics

Housing affordability is a significant concern in many parts of the world, affecting the quality of life and economic wellbeing of individuals and families.

To understand the dynamics of housing affordability we need to take a detailed look at a range of different metrics and statistics to gain a full picture.

The Three Primary Methods of Real Estate Data Integration

cLearn the three primary methods of real estate data integration—geospatial relationships, title matches, and address matching—to improve accuracy, insights, and decision-making.

What’s the Difference Between GDA94 and GDA2020?

Geodetic datums, or geodetic systems, are often used by proptechs. Here is a rundown of everything you need to know about the different geodetic datums we use and reference in Australia.

Alternative Data: What Is It, Who Uses It And Why It Matters

Discover the powerful intel alternative data can offer. Learn why businesses and investors are turning to non-traditional data sources for deeper insights and smarter decisions.

How to Use Snowflake Marketplace Without an Account

Apr 12, 2023 | Technology Application

You’ve seen a listing on the Snowflake Marketplace (hopefully from The Proptech Cloud 🙂 ) and you want to access the data for your business or project, but don’t have a Snowflake account – what do you do?

Keep reading, we will show you how to get started.

Prerequisites

You have a Cloud account (AWS is The Proptech Cloud’s preferred but GCP and Azure work just as well), and 5 to 10 mins to spare time!

Step 1 – Get access to Snowflake

The easiest and fastest path is to start a free 30-day trial of Snowflake from the Snowflake website.

Read more about the Snowflake trial accounts before you get started.

Note: Once the trial expires you will need to enter credit card in order to keep using Snowflake.

A trial account is enough to get you access and started, but for ongoing integration with the Marketplace you are better off converting to a paid account by entering a credit card.

When setting up your account, ensure the region and Cloud provider matches your Cloud Region and Provider.

An alternate approach is to request the data provider create a Reader Account for you on Snowflake. Given there are costs associated with this for the data supplier, it isn’t likely option for free Snowflake Marketplace listings. For paid subscriptions, particularly listings that have higher pricing, this could be a possibility.

The following diagram depicts how both options work (note the Consumer Account is where consumer starts their own Snowflake account):

Because a Reader account does not have a licensing agreement with Snowflake, support services are not available to the general users in the account. Instead, as the provider of the account, you field questions and requests from users in the account and respond as appropriate.

Step 2 – Setting up your Cloud environment

The following section will describe a set up on AWS, but similar steps are required for each of the major Cloud providers.

The following diagram depicts the type of infrastructure we will be setting up.

Create a S3 Bucket

a. Log into the AWS Management Console.

b. From the home dashboard, choose S3.

c. Click Create bucket

d. Enter a Bucket name and any other information your organisation requires.

e. Click Create bucket to finish the creation.

Create an IAM role that Snowflake can assume to access your S3 bucket

1. From the home dashboard, choose IAM.

2. Choose Account settings from the left-hand navigation pane.

3. Expand the Security Token Service Regions list, find the AWS region corresponding to the region where your account is located, and choose Activate if the status is Inactive.

4. Choose Policies from the left-hand navigation pane.

5. Click Create Policy:

6. Click the JSON tab.

7. Add a policy document that will allow Snowflake to access the S3 bucket and folder.

The following policy (in JSON format) provides Snowflake with the required permissions to load or unload data using a single bucket and folder path. You can also purge data files using the PURGE copy option.

Copy and paste the text into the policy editor:

Note: Make sure to replace bucket and prefix with your actual bucket name and folder path prefix.

Note: Setting the “s3:prefix”: condition to either [“*”] or [“<path>/*”] grants access to all prefixes in the specified bucket or path in the bucket, respectively.

8. Click Review policy.

9. Enter the policy name (e.g. snowflake_access) and an optional description. Click Create policy.

10. Choose Roles from the left-hand navigation pane.

11. Click the Create role button.

12. Select AWS account as the trusted entity type.

13. In the Account ID field, enter your own AWS account ID temporarily. Later, you will modify the trusted relationship and grant access to Snowflake.

14. Select the Require external ID option. Enter a dummy ID such as 0000. Later, you will modify the trusted relationship and specify the external ID for your Snowflake stage. An external ID is required to grant access to your AWS resources (i.e. S3) to a third party (i.e. Snowflake).

15. Click the Next button.

16. Locate the policy you created in Step 1: Configure Access Permissions for the S3 Bucket (in this topic), and select this policy.

17. Click the Next button.

18. Enter a name and description for the role, and click the Create role button.

You have now created an IAM policy for a bucket, created an IAM role, and attached the policy to the role.

19. Record the Role ARN value located on the role summary page. In the next step, you will create a Snowflake integration that references this role.

Step 3 – Create a Storage Integration on Snowflake

Create a storage integration using the CREATE STORAGE INTEGRATION command.

A storage integration is a Snowflake object that stores a generated identity and access management (IAM) user for your S3 cloud storage, along with an optional set of allowed or blocked storage locations (i.e. buckets).

Cloud provider administrators in your organisation grant permissions on the storage locations to the generated user. This option allows users to avoid supplying credentials when creating stages or loading data.

A single storage integration can support multiple external (i.e. S3) stages. The URL in the stage definition must align with the S3 buckets (and optional paths) specified for the STORAGE_ALLOWED_LOCATIONS parameter.

Note: Only account administrators (users with the ACCOUNTADMIN role) or a role with the global CREATE INTEGRATION privilege can execute this SQL command.

Step 3 Create a storage integration in Snowflake

Where:

integration_name is the name of the new integration.
iam_role is the Amazon Resource Name (ARN) of the role you created in Step 2 in AWS (in this topic).
bucket is the name of a S3 bucket that stores your data files (e.g. mybucket). The required STORAGE_ALLOWED_LOCATIONS parameter and optional STORAGE_BLOCKED_LOCATIONS parameter restrict or block access to these buckets, respectively, when stages that reference this integration are created or modified.
path is an optional path that can be used to provide granular control over objects in the bucket.

Step 4 – Link Snowflake Storage Integration to AWS IAM Role

Execute the DESCRIBE INTEGRATION command to retrieve the ARN for the AWS IAM user that was created automatically for your Snowflake account:

Where:

integration_name is the name of the integration you created in Step 3: Create a Storage Integration on Snowflake (in this topic).

Record the following values:

VALUES	DESCRIPTION
STORAGE_AWS_IAM_USER_ARN	The AWS IAM user created for your Snowflake account, arn:aws:iam::123456789001:user/abc1-b-self1234 in this example. We provision a single IAM user for your entire Snowflake account. All S3 storage integrations use that IAM user.
STORAGE_AWS_EXTERNAL_ID	The external ID that is needed to establish a trust relationship.

You will provide these values in the next section.

Step 5 – Grant the IAM User Permissions to Access Bucket Objects

The following step-by-step instructions describe how to configure IAM access permissions for Snowflake in your AWS Management Console so that you can use a S3 bucket to unload data:

1. Log into the AWS Management Console.

2. From the home dashboard, choose IAM

3. Choose Roles from the left-hand navigation pane.

4. Click on the role you created in Step 2: Setting up your Cloud environment (in this topic).

5. Click on the Trust relationships tab.

6. Click the Edit trust relationship button.

7. Modify the policy document with the DESC STORAGE INTEGRATION output values you recorded in Step 4: Link Snowflake Storage Integration to AWS IAM Role (in this topic):
Policy document for IAM role

Where:

snowflake_user_arn is the STORAGE_AWS_IAM_USER_ARN value you recorded.
snowflake_external_id is the STORAGE_AWS_EXTERNAL_ID value you recorded.

8. Click the Update Trust Policy button. The changes are saved.

Step 6 – Set up Snowflake to Extract to AWS

Option 1: Non-Reader Accounts:

If using a paid Snowflake Account, create an external (i.e. S3) stage that references the storage integration you created.

Note: Creating a stage that uses a storage integration requires a Snowflake role that has the CREATE STAGE privilege for the schema as well as the USAGE privilege on the storage integration.

For example:

Where:

integration_name is the name of the integration you created in Step 3: Create a Storage Integration on Snowflake (in this topic).
myrole is the name of the Snowflake role you are using for this process

1. Create the stage using the CREATE STAGE command.

For example, set mydb.public as the current database and schema for the user session, and then create a stage named my_s3_stage. In this example, the stage references the S3 bucket and path mybucket1/path1, which are supported by the integration. The stage also references a named file format object called my_csv_format:

Where:

integration_name is the name of the integration you created in Step 3: Create a Storage Integration on Snowflake (in this topic).

2. Use the following code to create a scheduled Task to unload the Snowflake Marketplace data to your S3 bucket –

Where

my_file_format should be a file format you have created. See these instructions for creating a file format – CREATE FILE FORMAT | Snowflake Documentation
my_external_stage is the stage you created in the prior script to this one.

Note: update the schedule according to your requirements. the format here is cron

Option 2: Reader Account:

If using a Reader Snowflake Account, use the following code to unload to S3-

Where

my_file_format should be a file format you have created. See these instructions for creating a file format – CREATE FILE FORMAT | Snowflake Documentation
your_bucket is the AWS Bucket from Step 2 – Setting up your Cloud environment
path/to/files/is a location from the AWS Bucket from Step 2 – Setting up your Cloud environment

And that’s it. You now have a scheduled Task to unload (copy) data out of the Snowflake Marketplace into your Cloud provider.

If you have a Reader Snowflake Account then there is no cost to run the Task. If you have a paid account, you will pay for the compute required (processing time) by the Warehouse which will vary by data listing on the Marketplace.

Coordinate Reference System (CRS)

Geodetic Datum

What’s the difference?

The main differences between Coordinate Reference Systems and Geodetic Datums

The decision between which CRS or geodetic datum to use

Geographic Region

Application

Desired Level of Accuracy

Practical uses for CRS and geodetic datums

How to set these in Snowflake

Read more from The Proptech Cloud

Geohash: A Familiar Friend

H3: The Challenger

Comparing Geohash and H3

Geohash or H3: Choosing the right system

Snowflake releases H3 functionality

Read more from The Proptech Cloud

What is a geohash?

How geohashes make geospatial joins more efficient

Snowflake and ST_GEOHASH

Geohash – Streamlining geospatial joins

Read more from The Proptech Cloud

Prerequisites

Step 1 – Get access to Snowflake

Step 2 – Setting up your Cloud environment

Create a S3 Bucket

Create an IAM role that Snowflake can assume to access your S3 bucket

Step 3 – Create a Storage Integration on Snowflake

Step 4 – Link Snowflake Storage Integration to AWS IAM Role

Step 5 – Grant the IAM User Permissions to Access Bucket Objects

Step 6 – Set up Snowflake to Extract to AWS

Option 1: Non-Reader Accounts:

Option 2: Reader Account:

Read more from The Proptech Cloud

Recent Posts

Recent Comments

Data Listings