Month: March 2021

Postgres – create a trigger to automatically calculate area of Polygon on edit

This will calculate the area in hectares of a polygon on creation or edit..( referenced here as Before Insert or Update On)

This appears to work for polygon and multi-polygon geometry types I have had a discussion with a colleague and he has indicated that the type of project may affect the accuracy of the measurement. Certainly there is a straight division in there so it would probably be possible to alter the division figure by empircally testing to get it as accurate as possible. Apparently with some popular projections the further north you are the less accurate the resultant figure. (Just something to bear in mind).

CREATE OR REPLACE FUNCTION calc_area() 
RETURNS trigger AS $BODY$ 
BEGIN NEW.grossarea := ROUND((st_area(NEW.geom)/10000)::numeric,2); 
RETURN NEW; 
END; 
$BODY$ LANGUAGE plpgsql;

What I found interesting about this was the field name is defined in the function and called from the trigger. There is possibly a better way of writing this to define the field in the trigger itself..

And the trigger that goes on the table

CREATE TRIGGER area_calculate BEFORE INSERT OR UPDATE ON public.t001landparcels FOR EACH ROW EXECUTE PROCEDURE calc_area();

CPANEL setting up ALLOW and Deny on IP addresses / forcing HTTPS

Two changes to the .htaccess that are easy to implement and will improve the security of PHP websites.

If you have subdomains that you would like to restrict access to and they are running a PHP site. You can use the .htaccess file to allow or deny access.

A very uncomplicated way is if you have a particular site that you would like to ringfence why not just reduce access to it to one location… Your home..

<RequireAny>
require ip x.x.x.x
require ip y.y.y.y
</RequireAny>

You can also use this to allow or block entire countries

Block a country from your site

And if you would like to find out the IP address range required to block you can find that here.

IP2location.com

The nice thing about this is that it works on subdomains. Using the CPANEL file manager just go into the subdomain find the htaccess file and block accordingly.

And

Force all of your pages to use HTTPS. To do this you will need to modify your .htaccess file (or create one if it doesn’t exist)

Using the Code Editor in the file manager, add these lines to the beginning of the .htaccess file

RewriteEngine On
RewriteCond %{HTTPS} off
RewriteRule ^(.*)$ https://%{HTTP_HOST}%{REQUEST_URI} [L,R=301]

Installation of Nubuilder through CPANEL

Background – I wrote sometime ago about an open source low code project called nubuilder originally started by a Steven Copely – it has been steadily and consistently developed over the years and Steven has since shared development with some other talented individuals. I took another look at it recently to see whether  I could find a way of installing and playing about with it on my existing web hosting provider…

and…

If you have a webhost that allows you access to a CPANEL client to configure your hosting environment then it is highly likely that you can set up a nubuilder low code environment. If you have a webhost that allows access to a CPANEL account AND allows you unlimited sub domains and unlimited storage then it is highly likely that you will be able to create a nubuilder low code environment for zero cost!!! 

A quick search online found that the following offer CPANEL / unlimited subdomains / unlimited MYSQL

A2Hosting

WebhostingHub (which are a subsidiary of InMotion)

SiteGround

There is usually a limit on the size of an individual MYSQL database but it is so high as to not be an issue.

Configuration Instructions:

This post was adapted from Steven Copleys video which can be found here..

Installing on Bluehost

First below I have blanked out my cpanel username variable with either a grey circular box or in code I’ve replaced it with an X. This variable is implemented by CPANEL software to allow resellers to ensure that thousands of users on the same servers do not accidentally duplicate directory and filenames. The username can only be accessed with an additional password but it is not generally available to anyone but the host admin and as such is additional security. It will be used in many of the default settings when creating things like databases and directory names.

Firstly Log into your providers CPANEL

Look to the Domains section and within it there should be a Subdomains icon.

and select the Subdomains icon

Create a new subdomain

Here is the Subdomain field I have typed nbexample – the document root will automatically be completed

Hit the Create button

Next we create a database

go to MySQLDatabases within the Databases menu

Now we create a New User

Here I do the same

username will be X_nb4exampledbuser

password

BlueSkyIsEverywhereToday2020

Hit the create user and Go Back and add the user to the database

On hitting add you will be asked to select the privileges that the user has over the database just indicate that you would like to allow them all privileges

Then hit the make changes button and you can if you want then check the MySQL databases and check that the database exists and that the user is there.

Next we go to the Nbuilder Github site and download the master file but zipped

Nubuilder 4.5 on GitHub

And select the Dowload ZIP option from the Code drop down list.

This will download a master file to your dowload folder – In your browser window in the bottom left you will see the file like this

You now need to go back to the main CPANEL hub and look for section marked Files and look for the File Manager option. In my CPANEL it looks it is a red icon.

The subdomain will be created as a directory within your root home directory and in my example looks like this. You will be transferring the master zip file into this directory so you want to select it.

Next hit the Upload button and navigate to find

nubuilder-4.5-master.zip

And load it into your base directory. It should look something like this now

Next you want to extract it..

It will then ask where you wish to extract it to

I will normally not put anything in here as it creates its own directory.

There will be a short delay after which you will be presented with a dialog that shows the outcome of the extraction process.

You should now see a new directory in which in my case is called nuBuilder-4.5-master.

I don’t like hypens dots and capitals or special characters in directory names. So

  • I rename it to nubuilder45
  • Convert to all lower case
  • Delete the old zip file

Next we want to go into the nuBuilder45 file and open up the nuconfig.php file.

This is where we will link allow the nubuilder php  to link to the created mysql database.

Right mouse click and select edit.

And find the following lines

Now remember from the start our database name / user and password are as follows.

  • Database : X_nb4exampledb
  • Username : X_nb4exampledbuser
  • Password : BlueSkyIsEverywhereToday2020

And hit the changes..

Next we want to create a very simple index.php file – alter the path to suit your subdomain / domain and directory where you extracted the nubuilder master file to.

<!DOCTYPE html>
<html>
<body>
<script type="text/javascript">
window.location.replace("https://nbexample.cloudydatablog.net/nubuilder45")
</script>
</body>
</html>
</body>
</html>

It may well look like this

This simple index.php file is placed in the root of the new subdomain you created

Now you should be able to go to the new subdomain in any browser on the planet and …

 

Congratulations you have successfully configured a default instance of nubuilder – your journey to creating low code online databases can now begin..

PS the default credentials are

Username : globeadmin

Password : nu

Please change these immediately in the nuconfig.php file when you get a chance.

For more information on starting to design and develop with nubuilder please follow the link below

link