Learning a complex skill like programming can be tough. Doesn’t have to be. But when you’re starting out you will be experiencing those two block that will hold you back.

And yes this is totally normal. Even developers that have years of experience under their belts will experience those block from time to time. It’s just our natural reaction when we need to learn something new.

Watch this video to find out how to deal with those two blocks, get unstuck and get to your personal next level of programming (whatever it might be right now)

Data is in the center of every GIS system, so when you know how to control it it’s like having GIS super powers. Let’s see what does that actually mean.

What’s control anyway?

First, let’s clarify what does it mean to have control over data. In think that in the context of GIS it means that you have ability to:

access it, wherever it is located

filter it, get exactly what you need

change it, mold it as you wish

show it, present it in a way that it will be useful and understood

Notice that no matter if you make a map for a local county or create a cool one for New York Times you have to go through all four steps.

The specific tools might change,but I don’t think that this model will change that much over time. You can see that there are some tools that simplify some steps or merge them (like some web apps).To stay in control we have to keep them in mind.

If you can do all four you have some basic control. You can gain more control by learning at least one different way to do any of them.

It’s even better if you can learn a way to do it faster or better (whatever the specific definition might be)

Why bother?

Well, because things are changing.Sometimes faster and sometimes slower.

And if you know what gives you more control you can prepare yourself for a change.

Loosing control sucks.

So, how to keep it?

Have your eyes and ears open. Focus. Learn at least one different way of doing each step every two months. Try new things. Experiment.

But I…

I know.You don’t have time or maybe you don’t think that some tools are for you. Well, keep it small and simple. Get into habit of learning things. After a while it will be easier to just pick something up and play with it.

The Ultimate Control is…

when you realize that it’s all within your reach. It might take some time,but it’s doable. It’s being flexible when looking at problems. Looking at “new” as opportunity not a threat.

The speed of learning a new skill is very individual thing. If you think that you should learn quicker than you do, you might think that you’re not smart enough or not capable enough. I don’t think that’s the case at all.

In other words you have certain expectations about how much time do you need to learn something. How do you know how long should it take? As with most things, we ask others for clues.

And that’s the first problem. We set our expectations based on someone else’s experience. Which can be totally off the charts.

Mainly because most of us doesn’t really want to admin how long did it really take to learn something hard. We want to look smart in the eyes of others, so we tend to shorten the learning time lines in our stories.

Plus programming is really broad subject and different people can understand different things by “learning programming”

So, what’s better measurement of success in learning programming?

A far better way to measure your success is to measure it by completion of a small and interesting project. Especially at the beginning forget about speed.

Set aside a regular, fixed amount of time to work on your project.

Remove the time pressure if you can. If you learn something complex like programming time pressure is a killer. It will slow you way way down.

And the surprising thing is, that you will move much quicker that you might except. You will be more focused. You will “get it” quicker.

To move at full speed you need to remove everything from your environment that distracts you including focusing on how fast are you doing.

If you feel overwhelmed by the amount of SQL resources out there, this simple tutorial is for you. I will show how to easily play around with SQL without installing anything. You will learn how to avoid common errors and keep your queries nice and clean. I will show how SQL relates to a spatial data so you can have a good understanding before you dive into it. After following this tutorial you will know the SQL basics and will be ready to go deeper.

Let’s first have a look at our database. You don’t need to install anything. Just visit this site and you’re set. Done. Forget about “big” databases that need a lot of time to install and setup. Right now, this is all we need.

The list of tables is on the right. Click on Customers.

Now, a table just come up under Result. All the data from Customers table is there. Have a look at “SQL Statement”:

SELECT * FROM [Customers];

This is probably the most heavily used query in the world.

To choose only some columns, replace a star symbol with a list of columns separated by a comma:

Put the first example into “SQL Statement” and click Run SQL. Now, do the same with the second example. Got it? Our Results table has changed. First we’ve got one column and then two. You can put more columns, but remember to put a comma between them.

SELECT * FROM [Customers] WHERE Country = 'Mexico';

This will show you only rows with the specific value in the column.

If you want to be even more specific you can look for more that one value in more than one column:

SELECT * FROM [Customers] WHERE Country = 'Mexico' AND PostalCode = '05033';

Here we will get only rows with the specific values for Country and PostalCode columns. Both values have to match the exact values to show up in the results.
And here is the next example:

SELECT * FROM [Customers] WHERE Country = 'Mexico' OR Country ='USA';

In this example, only one of the value from the columns have to match. If both does not match we will get nothing.

Remember that you can use more than two columns and values with AND and OR. You can also put both in the same query.

For now, just keep in mind that AND is really useful when you want to target rows with a specifc values in each column. On the other hand with OR you can easily get rows that have a number of alternative values.

What if you don’t know exactly what to look for? Let’s say that you’ve talked to ‘Isabel’ on the phone, but forgot her surname. You can use the percent sign to get every contact name that starts with her name:

SELECT * FROM [Customers] WHERE ContactName LIKE 'Isabel%';

This is called a wildcard and you can put it anywhere inside a value. For example to look for Isabel’s potential relatives you can use something like this:

SELECT * FROM [Customers] WHERE ContactName LIKE '%Castro%';

You can mix LIKE and = as much as you need to using OR and AND.

It’s time to play around with these examples. Run each, see if you understand them. Change column names and try some different values.

The Style Appreciation
You can write SQL in many ways, but to make your live easier follow those few simple rules:

Always put square brackets around a table name. Table names can contain spaces or words reserved for commands like “select” and if you use them without square brackets your query will fial

Same goes for putting apostrophes around column’s values. Always use them to
save yourself from “stupid” mistakes

End your SQL statements with a semicolon. Its not always required, keep it there just in case.

Format your SQL statements like our examples. It will help you understand what goes where and later on you will find it easier to read them.

Style is important when you want to share your queries with other people. If you follow these guidelines your queries will be easier to understand. And if you use your queries on a different system you will need to tweak it a lot less to make them work.

What?! My SQL won’t work everywhere?
Yes, it sometimes won’t work the way you intended. Let me explain.
There are many database systems that uses SQL. And while SQL is an ISO standard each system might use a different way to implement a specific command.

All queries that you’ve seen so far will work everywhere. Assuming you have the same table and the same column names. One useful “extension” to SELECT won’t. SELECT TOP will show you only a specific number of rows. This will work on our database:

SELECT TOP 10 * FROM [Customers];

This will work on some systems but on other a slightly different one will work instead:

SELECT * FROM [Customers] LIMIT 10;

and again there are some systems where the following will work:

SELECT * FROM [Customers] WHERE ROWNUM <= 10;

And if you try the last one on our database you will get an error.
That’s pretty bad, right? We have three versions of the same command and some of them work and other doesn’t. Not really.

If you know that you can do something like that with SQL. And you understand how SELECT works. Finding the right version for your specific system is quick as wink.

For now, focus on understanding the most important concepts. Let the search engine take care of the rest.

The same goes for working with a spatial data. You will use the same commands
to work with it and the same basic concepts. But you have to know a bit more to get to the spatial part.

In a minute I will talk about data types and functions. Data types define what can you store in a table.Functions do work on your data for you. You need to understand both to work a spatial data.

Now let’s get back for a minute to our LIMIT examples. Remember that the last version,the one with ROWNUM after WHERE? This statement will fail on our system. You will see an error message on your screen after hitting Run SQL. It’s telling you that it can’t find ROWNUM column. Right. There is no such column in Customers table.

A specific error message can be different from system to system. One that you will see a lot when experimenting is a “syntax error”. It usually means that you’ve misspelled a command or forget some part of it. Look again at the SELECT examples. They have a certain structure,right? After a while you will get a feel of it. You’ll able to spot most of those error straight away.

Also, keep in mind tips from “The Style Appreciation” section. They eliminate some “stupid” errors and can save you a lot of learning time.

Ok, now back to data types. Data types control what you can put into each column.
They also control how long a data in each column can be. That way you always know what’s inside a table.

Some common data types in use are VARCHAR and INTEGER. Fist, can store a bunch of characters, second a number. Both have a maximum length. So you can’t go beyond that.
We won’t go deep into data types right know. Keep in mind that every column has one.

Data types are important for a spatial data too. To store a geometry we use data types like POINT, LINE and POLYGON. You will see in a spatial part how does this work exactly.

You can fully realize the power of geometries only when you consider what you can do with them.

This is where functions comes in. First of all, they let you do something with a data and get back changed results.

For example you can use LCASE function to change all the characters in a given column to lowercases:

SELECT LCASE(CustomerName) FROM Customers;

Look at the name of the column. Its pretty random. A better way to do this is to choose
something more readable for our new column:

SELECT LCASE(CustomerName) AS Customer FROM Customers;

AS let you create an alternative name for a column.You can also use it with a plain column to make it more readable:

SELECT CustomerName AS Customer FROM Customers;

So in the same way you can work with a spatial data. For example functions like LENGHT and AREA will measure a geometry. Pretty simple right? But consider doing it on a table that has one milion places. Databases are optimized to this kind of stuff quickly. Very quickly.